How can i retrieve a cell value based on two arrays

[mmaghraby]

Hi All,
I'm not an expert in Excel, and I've been trying to learn how to retrieve the green cell value with a formula.

So basically a formula that says the following: ( please refer to the attached Table)

Tableeeeee.png
1) Look for [F2] which is Brand 2
2) Look for the closest or nearest number to [F3] which is 15
3) Retrieve the cross matching exact value which is 5.

I've managed to use a formula to retrieve exact match (if I write 18.9 in [F3] I get the green cell, but if I put a number that is not in column A, the formula gives an N/A)

Would appreciate your help.

Image
Thanks,
Maghraby

[Glenn Kennedy]

Nearest -or nearest value greater than??

If value is 8

and choices are 7 and 9: which should be chosen?

[JohnTopley]

Do want to lookup the table value which has minimum difference for your search value i.e (18.9 -15) < (15-6.8 )?

Using

=INDEX($B$2:$D$4,MATCH($F$3,$A2:$A4,1),MATCH($F$2,$B$1:D$1,0))

will return 2 as MATCH (with data in ascending sequence) finds the largest <= search value i.e 6.8

[AliGW]

Try this:

=INDEX($B$2:$D$4,MATCH(F3,$A$2:$A$4,1)+1,MATCH(F2,$B$1:$D$1,0))

[Glenn Kennedy]

Ali, that breaks if the entered value = any of the values in the array. If the OP MEANS nearest larger value, the best way may be to rearrange the table in descending order...

[AliGW]

Ali, that breaks if the entered value = any of the values in the array. If the OP MEANS nearest larger value, the best way may be to rearrange the table in descending order...

True! And, yes - rearranging the data would help.

[Glenn Kennedy]

Alternatively, this returns the matching/next largest matching value, with the table as it stands...

=INDEX($B$2:$D$4,MATCH(SMALL($A$2:$A$4,COUNTIF($A$2:$A$4,"<"&$F$3)+1),$A$2:$A$4),MATCH($F$2,$B$1:$D$1,0))

[Tony Valko]

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.