Hello,
I have a conditional lookup type problem.
Below is an example  not the real  dataset of three arrays. For such a dataset, I would like to find the value of variable C for given values of variable A and variable B. For variable B, if the exact value is not in the dataset, I would like to be given the value C for the nearest B value.
So, if my search criteria are A = Z, variable B = 2.9 (which is not in the array) then I should return a value for the nearest B value (which is 3) for C being 0.3 (last row).
Example Dataset:
Variable A Variable B Variable C
X 1 0.1
X 2.9 0.2
X 3 0.3
Y 4 0.4
Y 5 0.5
Y 6 0.6
Z 1 0.1
Z 2 0.2
Z 3 0.3
I can find variable C for given A and B values by using the formula:
=INDEX(C2:C10,MATCH(1,(A2:A10=F2)*(B2:B10=G2),0))
However, this does not find the nearest value if the search value of B is not in the array (i.e., returns #N/A).
Also, I can find the nearest value of B in the array to my search B value using:
=INDEX(B2:B10,MATCH(MIN(ABS(B2:B10G4)),ABS(B2:B10G4),0))
However, the result is not conditional on the value of variable A and therefore in the example above would return the C value for A = X instead of A = Z.
Hopefully, this explains my problem – any help will be very much appreciated!
Cheers,
Lawrence
Look up nearest value with conditions



Re: Look up nearest value with conditions
Here is hopefully a clearer version of the data table.
Variable A , Variable B, Variable C
X , 1 , 0.1
X , 2.9 , 0.2
X , 3 , 0.3
Y , 4 , 0.4
Y , 5 , 0.5
Y , 6 , 0.6
Z , 1 , 0.1
Z , 2 , 0.2
Z , 3 , 0.3 
Re: Look up nearest value with conditions
Excel 2010 Tables
Conditional nearest number.
http://c3017412.r12.cf0.rackcdn.com/04_12_11.xlsx
If you get *.zip, don't unzip, just rename *.xlsx 
Re: Look up nearest value with conditions
Great that seems to work well, many thanks!

Re: Look up nearest value with conditions
I have used the formula a lot and it works great, but when I replace 2.94 with a 1 in the second table the name that is returned is incorrect. Is there any way to fix this problem?


Re: Look up nearest value with conditions
Caveat: The number in Table2 has to be within the X, Y or Z range of Table1.
1.0 is outside the range of 2.8 to 3.0.
Fix: Redefine the minimum Table1 X range to below 1.0
Added data validation restriction to enforce this rule.
Same link. 
Re: Look up nearest value with conditions
Intern78,
Welcome to Ozgrid.
Do not post your questions in thrreads started by others   this is known as thread hijacking.
ALWAYS start a new thread for your questions and if you find it helpful to clarify your needs you may include a link back to this (or any other) thread.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!