lookup tables

[Phil Newman]

ok,

i have a value, 15.086, which i want to look up in a table of values
which are all integers, shown below.

10
12
18
22
33


Mathematically, the closest value is 18. However, when i use VLOOKUP
it tells me that 12 is the closest, because it searches down the list,
and not up.

Is there a way of searching up the list, or both up and down, in order
to find the true closest value?

Regards,

Phil Newman

 

[Bernard Liengme]

A bit long winded but
=IF(ISNA(VLOOKUP(A3,$E$1:$E$5,1,0)),INDEX($E$1:$E$5,MATCH(A3,$E$1:$E$5,1)+1),VLOOKUP(A3,$E$1:$E$5,1,0))
The value to lookup (15.086) is in A1 and the table of values in E1:E5
The INDEX/MATCH does the advancing by 1 but we need to avoid this if an
exact match is found.
best wishes

 

[Phil Newman]

blimey, that IS long-winded! but it works great, so thanks very much!

Phil

 

[Phil Newman]

actually, that doesn't work too great, because it now only chooses to
go up, rather than decide which is closer...

for example, i have 33.8, and the values are 33 and 39. it now chooses
39 as the closest!

Phil

 

[Pete_UK]

Phil,

here's one which returns the minimum difference between the two closest
values;

=MIN(I1-INDEX(H1:H5,MATCH(I1,H1:H5)),INDEX(H1:H5,MATCH(I1,H1:H5)+1)-I1)

with your integers in H1:H5 and the value you are trying to match in
I5. I've not sussed out yet how to convert this back into one of the
integers - maybe another cell with an IF in it ...

Hope this helps for now ...

Pete

 

[Bernard Liengme]

But you did not ask for that!
What you want when the test-value is exactly midway? Say 15, which is 3 away
from 12, and 3 away from 18. Do you want the larger or the smaller answer?
Can you cope with a User Defined Function in VBA if I make one up?
See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes

 

[Roger Govier]

Hi Phil

Try the array entered formula
{=INDEX(A1:A5,MATCH(MIN(ABS($B$1-A1:A5)),ABS(($B$1-A1:A5)),-1))}

To enter or edit and array formula, use Ctrl+Shift+Enter (CSE) instead
of Enter.
Do not type the curly braces { } yourself, Excel will enter then when
you use CSE.

 

[Phil Newman]

quote: "Is there a way of searching up the list, or both up and down,
in order to find the true closest value?"

in the unlikely case where the non-integer value lies exactly between
the two integers, the larger integer will suffice.

yes, i can cope with a user-defined function, that would be very nice
thank you.

Regards,

 

[Phil Newman]

Thanks,

that seems to work well.

how come i need to edit the array with CSE?

Phil

 

[Bernard Liengme]

This is very clever.
When B1 is 17, the inner part becomes =MATCH(1,{7;5;1;5;16},-1)
But the array is not in descending order as specified by Help.
I suppose this does not matter since the match is found before the number
start to increase?
Any comment?

 

[Dave Peterson]

I would think that using 0 (for an exact match) instead of -1 would be safer.

 

[Pete_UK]

Phil,

I needed a few hours' break to get something to eat - I see you've had
some other responses. Here's a non-array formula in one cell that
returns the closest integer from your table (assumed to be H1:H5 as
before, with the number to be matched in i5. If your number lies
exactly midway between 2 integers, this returns the lower integer:

=IF(VLOOKUP(I1,H$1:H$5,1)+MIN(I1-VLOOKUP(I1,H$1:H$5,1),INDEX(H$1:H$5,MATCH(I1,H$1:H$5)+1)-I1)=I1,VLOOKUP(I1,H$1:H$5,1),INDEX(H$1:H$5,MATCH(I1,H$1:H$5)+1))

Vlookup is used for "normal" match (closest, lower) and INDEX/MATCH for
the next higher number.

Hope this helps.

Pete

 

[Roger Govier]

Hi Bernard

The array being tested is in ascending order in the OP supplied and in
my test.
Therefore, the ABS() of the difference between the test and the array is
descending at first and then begins to rise.
As you rightly say, because we are looking for the MIN, that gets found
before the numbers begin to rise again, hence the solution seems to work
with the -1 parameter.
I cannot get it to work with 0, or with 1. It sometimes works with 1, if
the value being tested is integer, but not with a decimal value.

Whilst in the true sense, it does not follow the rule for Index, because
of the Min and Abs it seems to work.

Having just written the above, I did just create another list with
numbers in random order, and again the formula works with -1 but fails
with either 0 or 1.
I am now at a loss to explain why it works, other than as far as my
testing seems to be concerned - it does!!!!

 

[Roger Govier]

Hi Phil

All array formulae must use Ctrl+Shift+Enter when initially created, or
when edited, otherwise Excel treats them as standard formulae, and will
not work upon each of the elements in an array manner.

 

[Roger Govier]

Hi Dave

I don't think we could use 0, as it is unlikely that an exact match will
ever occur.

 

[RagDyer]

This works for me with *or* without a sorted data list:

=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-B1)),ABS(A1:A5-B1),0))

 

[Roger Govier]

Hi Dave

Retract that. I'm being an idiot (yet again !!!)
Of course the difference is bound to exist in the list of differences. I
was thinking of the data value being looked up.
Apologies.

--
Regards

Roger Govier

Hi Dave

I don't think we could use 0, as it is unlikely that an exact match
will ever occur.

 

[Roger Govier]

Hi RD

I have just re-tested my data and I agree it works both ways with 0 as
the parameter.

Lots of blushes - I didn't press CSE when I switched from -1 to 0!!!!!

 

[Dave Peterson]

I thought that maybe you came across one of those 15 significant digit rounding
differences that reared its ugly head when min() was added to abs().

Hi Dave

Retract that. I'm being an idiot (yet again !!!)
Of course the difference is bound to exist in the list of differences. I
was thinking of the data value being looked up.
Apologies.

 

[Roger Govier]

Hi Dave

No, just me being very tired at the end of a long day.
Better get off to bed and get some sleep, then maybe I will start
thinking clearly (again?)