Lookup Function - difficulties

Posted by Chase on September 06, 2001 3:31 PM

Hi all,

I by an earlier post - was recommended to try the lookup function. Basically we have one sheet ("figures" sheet) with nothing but model codes, model name and trim level.

When on sheet1, cell H9 equals a model code on "figures" sheet - let two additional cells on sheet1 pull cooresponding model name and trim level from that row that matches model code.

The lookup formula did indeed pull, but it pulled seemingly random. I couldnt determine any particular method to it's maddness. All column's & rows are same heigth.

Any Ideas?

Posted by Aladin Akyurek on September 06, 2001 4:06 PM

Chase,

There are many "lookup" functions in Excel, LOOKUP is one of them. I believe Riichard's suggestion was about doing a lookup, not about using the LOOKUP function in particular.

In fact, you can use the VLOOKUP function for your problem.

On the "figures" sheet do the following:

Select all the cells where you have "model codes" (except the column heading), name the selected range MCODES via the Name Box on the Formula Bar.
Select all cells where you have "model codes", "model names", and "trim levels" and name the selected range MODELS via the Name Box.

On Sheet1

in I9 enter: =IF(LEN(H9)>0,IF(ISNUMBER(MATCH(H9,MCODES,0)),VLOOKUP(H9,MODELS,2,0),""),"")

in J9 enter: =IF(LEN(I9)>0,VLOOKUP(H9,MODELS,3,0),"")

Aladin

=========

Posted by John on September 06, 2001 4:20 PM

It sounds like you might not be using the last argument in the VLookup function:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

range_lookup must be False if the first column of the table_array is not sorted.

Posted by John on September 06, 2001 4:21 PM

It sounds like you might not be using the last argument in the VLookup function:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

range_lookup must be False if the first column of the table_array is not sorted.

Posted by Chase on September 06, 2001 4:34 PM

thank you Aladin!

Holy cow,

Not sure how the whole thing worked - I can kinda piece some together, but it does. Hot diggity.

Thanks!!!

Posted by Chase on September 06, 2001 4:39 PM

Thanks John

Thanks John.

I got it working with Aladin's method, however I'm going to try to make sure it's sorted - just to see if that does it or not. Can never learn enough.

Thank you!

Chase