MS Excel using multidimensional lookup

Question

Hi am trying to assign a value given that if I have a some value that matches column 1, it will search try to check if for that specific row my my product variable is lesser than specified.

So let's say I have colA_Var = 109, and rowB_Var = 12. I will search first column if the value exist then I need to compare if my rowB_Var < than values in row 109. So it will return 11.8 since its lower than 12. Another example if I have colA_Var = 179 and rowB_Var = 55 it will give me values 44 and 50 or one of them. So far i've tried the formula below but its not working. This should only be done using Excel functions as VM Macro's are not allowed.

= LOOKUP(Var_Const!D6,D_Male!$A$2:$A$84,D_Male!$B$2:$L$84)

Sample Data in Excel

108.0   11.6    12.9    15.2    16.5    18.1    20.0    22.1    23.1    25.1    27.0
109.0   11.8    13.1    15.6    16.9    18.5    20.3    22.6    23.9    25.7    27.5
178.0   43.5    49.2    55.9    62.0    68.5    74.5    81.0    87.0    93.2    100.0
179.0   43.8    49.7    56.0    62.5    69.0    75.2    81.3    87.3    94.0    100.8
180.0   44.0    50.0    56.2    62.7    69.5    76.0    82.0    88.0    94.5    101.2

Answer 1

With

• Var_A in L1C2
• Var_B in L2C2
• The result in L3C2
• Values in L5C1:L9C11

My forumula is

=HLOOKUP(L2C2,INDIRECT(ADDRESS(4+MATCH(L1C2,L5C1:L9C1),2)&":"&ADDRESS(4+MATCH(L1C2,L5C1:L9C1),11)),1,TRUE)

Explanation : Search the appropriate line according Var_A value, construct the reference of an array of only one line, search in it the Var_B value.

Answer 2

I propose this function:

=HLOOKUP(A8,INDEX(B1:K5,MATCH(A7,A1:A5,0),0),1)

INDEX(B1:K5,MATCH(A7,A1:A5,0),0) returns the row on which Var_B is to be looked for.

The HLOOKUP then looks for the value smaller than Var_B.