Closest match insert value

[strongman86]

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.

	A	B	C	D	E	F	G	H	I	J
1	Unit size	Type					Type	Type size
2	280						Red	36
3	64						Blue	78
4	96						Yellow	108
5	175						Green	229
6	20						Orange	300

<tbody>
</tbody>

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H
1	Unit size	Type					Type	Type size
2	208	Yellow					Red	36
3	64	Red					Blue	78
4	96	Blue					Yellow	108
5	175	Yellow					Green	229
6	20	*Not Found*					Orange	300
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX($G$2:$G$6,MATCH(A2,$H$2:$H$6,1)),"*Not Found*")

WBD

 

[FormR]

Or another very similar option:

=IFERROR(LOOKUP(A2,$H$2:$H$6,$G$2:$G$6),"Not Found")

 

[strongman86]

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.

	A	B	C	D	E	F	G	H
1	Unit size	Type					Type	Type size
2	280	Orange					Red	36
3	64	Blue					Blue	78
4	96	Yellow					Yellow	108
5	175	Green					Green	229
6	20	Red					Orange	300

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

 

[wideboydixon]

Love it when the question completely changes ... :-|

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

Book1
	A	B	C	D	E	F	G	H
1	Unit size	Type					Type	Type size
2	280	Orange					Orange	300
3	64	Blue					Green	229
4	96	Yellow					Yellow	108
5	175	Green					Blue	78
6	20	Red					Red	36
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX($G$2:$G$6,MATCH(A2,$H$2:$H$6,-1)),"*Not Found*")

WBD

 

[strongman86]

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.

 

[FormR]

I can't use sort

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
	A	B	C	D	E	F	G	H
1	Unit size	Type					Type	Type size
2	280	Orange					Red	36
3	64	Blue					Blue	78
4	96	Yellow					Yellow	108
5	175	Green					Green	229
6	20	Red					Orange	300
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX($G$2:$G$6,MATCH(TRUE,INDEX(A2<=$H$2:$H$6,0),0))

 

[wideboydixon]

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.

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

WBD