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


[TABLE="class: grid, width: 704"]
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[TD="width: 64"]H[/TD]
[TD="width: 64"]I[/TD]
[TD="width: 64"]J[/TD]
[TD="width: 64"]K[/TD]
[TD="width: 64"]L[/TD]
[TD="width: 64"]M[/TD]
[/TR]
[TR]
[TD="width: 64"]1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]810[/TD]
[TD="align: right"]=MIN(IF(K2:M2>D2,K2:M2))[/TD]
[TD]=INDEX(H2:J2,1,MATCH(E2,K2:M2,0))[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]790[/TD]
[TD="align: right"]800[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]1000[/TD]
[/TR]
</tbody>[/TABLE]


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.