Closest match insert value

S

strongman86

Board Regular
Joined
Feb 15, 2017
Messages
115
Office Version
  1. 2019
Platform
  1. Windows
Hi all,
Need formula in B2 that compares A2 cell value with column H values and find closest match that are either equal or lower than A2 value. Then it need to return value from corresponding column G which in this case is text (Orange). Thanks.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Unit size[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Type size[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]280[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]64[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Yellow[/TD]
[TD="align: center"]108[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]175[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Green[/TD]
[TD="align: center"]229[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes

Book1
ABCDEFGH
1Unit sizeTypeTypeType size
2208YellowRed36
364RedBlue78
496BlueYellow108
5175YellowGreen229
620*Not Found*Orange300
Sheet1
Cell Formulas
RangeFormula
B2=IFERROR(INDEX($G$2:$G$6,MATCH(A2,$H$2:$H$6,1)),"*Not Found*")


WBD
 
Upvote 0
Or another very similar option:

=IFERROR(LOOKUP(A2,$H$2:$H$6,$G$2:$G$6),"Not Found")
 
Upvote 0
Thanks Guys.
Formula works, but it's not exactly what I'm after. Just noticed that value needed to be higher not lower as I originally described. Sorry for confusion. See below what values needed to be returned in B column.

Unit sizeTypeTypeType size
OrangeRed
BlueBlue
YellowYellow
GreenGreen
RedOrange

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[TH="align: center"]H[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]280[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]36[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]64[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]78[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]96[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]108[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]175[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]229[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]300

[/TD]

</tbody>
 
Upvote 0
Love it when the question completely changes ... :-|

In which case you'd be better to sort your size list in descending order:


Book1
ABCDEFGH
1Unit sizeTypeTypeType size
2280OrangeOrange300
364BlueGreen229
496YellowYellow108
5175GreenBlue78
620RedRed36
Sheet1
Cell Formulas
RangeFormula
B2=IFERROR(INDEX($G$2:$G$6,MATCH(A2,$H$2:$H$6,-1)),"*Not Found*")


WBD
 
Upvote 0
Sorry again :)
Still doesn't work. I used =IFERROR(INDEX($G$2:$G$6,MATCH(A2,$H$2:$H$6,-1)),"*Not Found*") and it returned me *Not Found* in B2 / B3 / B4 / B5 & Orange in B6.
I can't use sort as column A on my original spreadsheet is about 100 rows long where column H is as you see 6 rows.
 
Upvote 0
strongman86 said:
I can't use sort
Click to expand...

Not sure why the number of rows would matter when sorting but, one option - which will return the first type that fits the crtieria.


Excel 2013/2016
ABCDEFGH
1Unit sizeTypeTypeType size
2280OrangeRed36
364BlueBlue78
496YellowYellow108
5175GreenGreen229
620RedOrange300
Sheet1
Cell Formulas
RangeFormula
B2=INDEX($G$2:$G$6,MATCH(TRUE,INDEX(A2<=$H$2:$H$6,0),0))
 
Upvote 0
strongman86 said:
Sorry again :)
Still doesn't work. I used =IFERROR(INDEX($G$2:$G$6,MATCH(A2,$H$2:$H$6,-1)),"*Not Found*") and it returned me *Not Found* in B2 / B3 / B4 / B5 & Orange in B6.
I can't use sort as column A on my original spreadsheet is about 100 rows long where column H is as you see 6 rows.
Click to expand...

You only needed to sort the rows in columns G/H. However, you now have a nice alternative from FormR.

WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
How to return closest value based on two criteria?
Replies
6
Views
417
Peter_SSs
Peter_SSs
B
  • Question Question
Closest Match in a Range
Replies
1
Views
382
Rijnsent
Rijnsent
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
211
StephenCrump
S
M
  • Solved
return value based on the number that matches any number in three columns
Replies
3
Views
151
MMM_84
M
M
  • Solved
Index and match values closest to zero and return the value on the left column
Replies
2
Views
393
mcva
M

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top