Hlookup to find nearest match then offset to retrieve the value required

[swaink]

Evening Everyone

I have been playing with this one for a while now and unable to find a solution, I have seen many references on the board around HLookup & Match but nothing that seems to cover what I am attempting.

I have a table and in Col D I have a list of dimensions in millimeters IE.230 etc

In columns H,I & J I have Banding indicators A, B & C

In columns K, L & M i have boundaries shown in millimeters 600, 800, 1000 this will vary going down the columns

Example: In D2 I have 810, in E2 I want to show the next nearest match from K2 =600, L2=800, M2 = 1000 so in this instance I would expect to pull back 1000

In F2 I then want to pull back a value which is offset in columns H,I &J in this case I would expect to see C

I have hit a wall and struggling to find a workaround and any help would be appreciated

 

[NiMip]

Hi there, here's one way that works for me. Note that in column E you have to hit CONTROL+SHIFT+ENTER instead of just enter, as it's an array formula

	D	E	F	G	H	I	J	K	L	M
1
2	810	=MIN(IF(K2:M2>D2,K2:M2))	=INDEX(H2:J2,1,MATCH(E2,K2:M2,0))		A	B	C	600	800	1000
3	790	800	B		A	B	C	600	800	1000

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

If you want just the one formula, they could be combined as follows (again using control+shift+enter) =INDEX(H2:J2,1,MATCH(MIN(IF(K2:M2>D2,K2:M2)),K2:M2,0))

 

[swaink]

Hi NiMip

This has been a great help and works exactly as I was aiming for, really appreciate the help thank you

 

[Aladin Akyurek]

In E2 enter:

=INDEX(K2:M2,MATCH(D2,K2:M2,1)+(LOOKUP(D2,K2:M2) < D2)))

In F2 enter:

=INDEX(H2:J2,MATCH(E2,K2:M2,0))

 

[swaink]

Hi Aladin good to hear from you

I tweaked your first formula slightly as I was getting #N/A if D2 was less than K2 but its working fine thank you

 

[Aladin Akyurek]

Hi Aladin good to hear from you

Good to have you back here

I tweaked your first formula slightly as I was getting #N/A if D2 was less than K2 but its working fine thank you

You are welcome. Thanks for the update.

You are welcome.