# Return the nearest value in an area

#### MartinL

##### Well-known Member
I am trying to return the Column header based on a variable being closest in value to values in an area table.
That may sound confusing so here is a little data to show you what is being asked for

I have the following variables
Country = Germany
SKU = 1235
Requested price = 36,290
The expected retuned value is: TP2

I attempted to work with this formula but failed miserably: {=INDEX(A3:A5,MATCH(MIN(ABS(C2:E6-Requested Price)),ABS(C2:E6-Requested Price),0))}
I obviously need to include Columns A&B (Country & SKU) somewhere in the formula

 Country SKU RRP TP1 TP2 UK 1234​ 65,198 45,632 27,000 UK 1235​ 65,198 45,632 27,000 France 1234​ 54,301 38,011 27,000 Germany 1234​ 54,173 24,000 19,800 Germany 1235​ 54,173 24,000 19,800

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### MartinL

##### Well-known Member
My Bad my example is flawed the example Requested price should say 20,290 for expected returned value to be TP2.
Saw this too late to be able to edit the question.....
sorry for the additional confusion

#### Fluff

##### MrExcel MVP, Moderator
+Fluff 1.xlsm
ABCDEFGH
1CountrySKURRPTP1TP2
2UK123465,19845,63227,000Germany
3UK123565,19845,63227,0001,235
4France123454,30138,01127,00020,000TP2
5Germany123454,17324,00019,800
6Germany123554,17324,00019,800
7
Main
Cell Formulas
RangeFormula
H4H4=LET(Fltr,FILTER(C2:E6,(A2:A6=G2)*(B2:B6=G3)),INDEX(C1:E1,XMATCH(MIN(ABS(Fltr-G4)),ABS(Fltr-G4),0)))

#### MartinL

##### Well-known Member
Hi @Fluff
I've not come across anything like that before, However I get #NAME error when I try to use your solution.

I'm running Excel for Microsoft 365 MSO (16.0.13127.21656) 32-bit

#### Fluff

##### MrExcel MVP, Moderator

Ok, you probably don't have the latest updates & so are missing the LET function. Try this instead.
Excel Formula:
``=INDEX(C1:E1,XMATCH(MIN(ABS(FILTER(C2:E6,(A2:A6=G2)*(B2:B6=G3))-G4)),ABS(FILTER(C2:E6,(A2:A6=G2)*(B2:B6=G3))-G4),0))``

#### MartinL

##### Well-known Member
Hi @Fluff

Whoa! Ok yes that works.
I have raised this with IT, would be good to see your other solution working

Thank you very much.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
0
Views
464

1,141,299
Messages
5,705,574
Members
421,399
Latest member
hjweiss00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back