Return the nearest value in an area

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,118
Office Version
  1. 365
Platform
  1. Windows
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

CountrySKURRPTP1TP2
UK
1234
65,19845,63227,000
UK
1235
65,19845,63227,000
France
1234
54,30138,01127,000
Germany
1234
54,17324,00019,800
Germany
1235
54,17324,00019,800
 

Excel Facts

Links? Where??
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
Joined
Oct 16, 2008
Messages
1,118
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
How about
+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
Joined
Oct 16, 2008
Messages
1,118
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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))
 
Solution

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,118
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top