Returning adjacent value based on closest match to another cell value

[yankeesrus07]

Hi everyone,

Have an otherwise-complicated tool that is referencing zip codes and performing many functions.

I'm aiming to do the following now (please reference the attached Excel file): Based on a cell value in column I (auto-generated from column G), I want to reference anywhere in column C with that value. From those specified rows (will vary), I want to find the closest value to column H within column B and return the corresponding value from column A.

If it's easier to follow the color coding, here that is: based on red (column I) that match those in column C, find the orange (column B) value that is closest to column H and return the corresponding yellow cell (column A).

For this example, based on column I, looking for TX zips in columns A-C, it should return 77502 since code 82 is closer to code 85 than code 90.

Sorry for any confusion. If there's any more info you would need, please let me know.

Thanks,

[Jonmo1]

What if H2 was 87
What if H2 was 88

?

[FDibbins]

Play around with this and see if it will work for you...
=INDEX($A$2:$A$6,MATCH(I2&H2,INDEX($C$2:$C$6&$B$2:$B$6,0))+1)

[FDibbins]

@ Jonmo, I know my suggestion is not perfect, but perhaps it will do. I could probably refine a bit

[Jonmo1]

I don't know how it will deal with the non exact match between H2 and column B

[yankeesrus07]

@ FDibbins, thank you for the great start to this syntax

Just would need a tweak to it. Re: Jonmo, the cell values in column H are dependent upon the value in column G. For this test file, I've just input the values alone.

So, J2 pulls over fine so long as H2 < B5. But instead, we're looking to find the closest value in general. So, if I were to put 86 in H2, it should return A5. Same with 87. But as soon as 88 is input, it should switch over to A6.

Does that make sense? What kind of syntax would need to be adjusted to accommodate for that? Regarding instances where it's equidistant (referencing value 85 between 80 and 90), there is no preference there (so long as it chooses one of the column A values that correspond to either 80 or 90 in column B).

[Jonmo1]

Try this array formula entered with CTRL + SHIFT + ENTER

=INDEX($A$2:$A$6,MATCH(MIN(IF($C$2:$C$6=$I$2,ABS($H$2-$B$2:$B$6),"")),ABS(IF($C$2:$C$6=$I$2,$H$2-$B$2:$B$6,9.99999999999999E+307)),0))

[yankeesrus07]

Initial tests are looking good! Thank you so much!

If something wonky happens, I know where to go!

[Jonmo1]

You're welcome.