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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
320
Peter_SSs
Peter_SSs
B
  • Question Question
Closest Match in a Range
Replies
1
Views
242
Rijnsent
Rijnsent
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
204
StephenCrump
S
M
  • Solved
return value based on the number that matches any number in three columns
Replies
3
Views
143
MMM_84
M
M
  • Solved
Index and match values closest to zero and return the value on the left column
Replies
2
Views
389
mcva
M

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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