Find closest values and return adjacent values

[John_B_Russell]

Hello!

I am attempting to find the closest value from a list and have it pull in the adjacent data that corresponds to that value in Excel 2010.

If you look at the attached document in sheet 1 -I am planning for a new store in the LA area, so for for planning purposes I need to pull in transaction and traffic history (highlighted in orange) based on similar sales volume (Highlighted in yellow).

So if I am forecasting this new store to have a sales of $2,500,00 then, I would need for the formula to look at sheet 2, find the closest value (-in this example Store 240 is closest) therefore pull in 58,500 as my transaction and 300,000 as my traffic.

If it could pull in averages for the 2 closest values that would also be helpful!

Thanks in advance!

[azumi]

Please find the file attach, hope this helps...
Using array formula

regards

[John_B_Russell]

Thanks for the help Azumi!

When I open the file on my end it #N/A's out, I attempted to duplicate the formula in my original file and I receive the same error. Not sure if my excel version 2010 has anything to do with it.

This is what my formula looks like:

=INDEX('Data Mapping'!$M$14:$M$223,MATCH(INDEX('Data Mapping'!$I$14:$I$223,MATCH(MIN(ABS('Data Mapping'!$I$14:$I$223-'Census Worksheet'!$G$21)),ABS('Data Mapping'!$I$14:$I$223-'Census Worksheet'!$G$21),0)),'Data Mapping'!$I$14:$I$223,0))

[azumi]

you need to confirm press CTRL+SHIFT+ENTER button together because this array formula, ENTER alone not works

[John_B_Russell]

Azumi, I am entering CTRL+SHIFT+ENTER on multiple locations I placed the formula and am still getting #N/A. Any other tips and or suggestions?

[John_B_Russell]

Hi Azumi! I kept playing with the formula and it now works! This is exactly what I was looking for. Thank you for your help!!!

[azumi]

Glad you find the way out