Look up nearest value with conditions

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:B10-G4)),ABS(B2:B10-G4),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

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

Great that seems to work well, many thanks!