Lookup nearest value

**KevinThomas** · 06-09-2009, 04:04 AM

Hi all, I understand the VLookup and Hlookup concepts, but I don’t know how to (if possible) lookup a nearest match.

I have added an example attachment.

If in E4 – I want to lookup the value of D4, from columns A:B to find the nearest match (4.5 in this instance) and return the value of H (B8) how can I do this?

**DonkeyOte** · 06-09-2009, 04:07 AM

Array Formula:

E4: =INDEX(B1:B26,MATCH(MIN(ABS(D4-A1:A26)),ABS(D4-A1:A26),0))
committed with CTRL + SHIFT + ENTER

**KevinThomas** · 06-09-2009, 04:11 AM

Thanks D.O - I wonder if it is possible without using an array formula though?

If I want to use more than one (which I do) they can be quite hefty and slow the file down.

**zbor** · 06-09-2009, 04:14 AM

=index($b$1:$b$26;match(d4;$a$1:$a$26;1)+1)

**KevinThomas** · 06-09-2009, 04:17 AM

Magic, thanks very much

**DonkeyOte** · 06-09-2009, 04:19 AM

zbor, that wouldn't work if D4 were say 4.1

KevinThomas, it will depend in part on how many variants of this calculation you're performing... the simplest non-array approach would be to store the ABS vaiance of A1:A26 to D4 in C1:C26 and then use a standard INDEX/MATCH as outlined by zbor

=INDEX(B1:B26,MATCH(MIN(C1:C26),C1:C26,0))

You can do this in non-array form without helpers

=INDEX(B1:B26,MATCH(MIN(INDEX(ABS(A1:A26-D4),0)),INDEX(ABS(A1:A26-D4),0),0)

but this is not efficient.

**zbor** · 06-09-2009, 04:21 AM

Why not? Work for me....

It will find lowest value and +1 will bring propper result

Edit: you are right.. but this might work:

=IF(ISNA(MATCH(D4;$A$1:$A$26;0));INDEX($B$1:$B$26;MATCH(D4;$A$1:$A$26;1)+1);VLOOKUP(D4;$A$1:$B$26;2;TRUE))

**DonkeyOte** · 06-09-2009, 04:33 AM

No that would not work either. The key is that you must calculate the actual variance of the values to the criteria and match the smallest variance.

**squiggler47** · 06-09-2009, 05:14 AM

Please Login or Register  to view this content.

Confirmed with CSE works!

I'm sure somebody will optimise the formula ;-)

**yogeshanu** · 03-30-2010, 09:09 AM

Sir, i have auto finance company and have more than 750 customers. i want creat due date reminder register.

Installment due in particular day those customers.

Please help me.

With regard