Sorry, another Excel lookup question.

Sorry, another Excel lookup question.

Didn't find your answer?

I have a list of measurements in a column, sawn timber sizes available from our suppliers. I think what I need is some form of lookup table/function to pick the correct order size form the list. I can't get lookup or Vlookup to do this as, if there is no exact match I need the next largest and these functions reply with a smaller figure.
I do not necessarily need a lookup table as all I need is to pick one from a list.
I'm sure Excel has a function to do this easily but can't find it.

Thanks in advance for your help

daniel bourdon

Replies (5)

Please login or register to join the discussion.

avatar
By ACDWebb
28th Apr 2004 10:44

and for even more ideas for lookups
Including "left" lookups (VLOOKUP looks to the right) have a look at Chip Pearsons Excel site - always a good source of ideas and help I find

Thanks (0)
avatar
By AnonymousUser
27th Apr 2004 17:23

Using INDEX and MATCH instead of LOOKUP (etc) in Excel
Splendid. Just to generalise, for anyone else landing here (or too lazy to look up (!) the link), it goes like this:

INDEX(Column to look up,MATCH(Cell to match, Column with desired result,0)

The 0 at the end indicates the type of match.

1: finds the largest value in the lookup column that is less than or equal to the cell to match and returns the corresponding value in the result column. The columns must be in ascending order.

0: finds the first value in the lookup column that is exactly equal to the cell to match and returns the corresponding value in the result column. The columns can be in any order.

-1: finds the smallest value inteh look up column that is greater than or equal to the cell to match and returns the corresponding value in the result column. The columns must be placed in descending order.

If the match type is omitted, it is assumed to be 1.

E.g. =INDEX($H$7:$H$16,MATCH(B21,$D$7:$D$16,0))

This looks up the value in cell B21 in the range H7 to H16 and if it finds an exact match it returns the corresponding value in the range D7 to D16.

This method generally gives more reliable results than, and is more flexible than, the lookup functions.

Thanks (0)
avatar
By AnonymousUser
27th Apr 2004 14:13

Yep, I reckon so
As Alan says, I think MATCH will do it. For more on this see Excel Help and also

https://www.accountingweb.co.uk/cgi-bin/item.cgi?id=95993

Thanks (0)
avatar
By ACDWebb
27th Apr 2004 13:52

Possibly MATCH?
That allows you to set a match type and -1 will give you:

the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order

to quote Excel Help

Thanks (0)
Simon Hurst
By Simon Hurst
28th Apr 2004 08:46

Look up wizard
Just a general point on lookups in Excel - there is a 'Lookup Wizard' included in 'Tools, Add-Ins' that can often take some of the pain out of constructing lookup formulae. Once installed, it appears as an additional option on the Tools menu.

Simon

Simon Hurst
www.tkb.co.uk

Thanks (0)