MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Lookup Function


Posted by Tim Pedersen on January 09, 2001 8:02 AM

I'm having a problem using the lookup function; I have a workbook set up with two worksheets. The second worksheet is one very large spreadsheet that is set up with all of the budget data by inventory class, along with two dozen columns of information about each class. I've set up the first worksheet as a "form" that an analyst can use to key in an inventory class. My desire is to be able to key in the inventory class and have several other fields on this first worksheet populate from the second worksheet using the LOOKUP function. I use a command such as "=lookup(C11,base2001!a4:d322)" to populate a field from the BASE2001 worksheet, where cell C11 on the first worksheet matches those inventory classes in column A of the second worksheet, and the desire is to populate this cell in the first worksheet from those values contained in column D in the second worksheet. I can get this to work, but it seems to work somewhat randomly - on some classes it works accurately, but in some other cases it either can't find a match or returns values from another row! Any ideas? Sorry this is so long!

Tim Pedersen


Posted by Jim Black on January 09, 2001 12:21 PM

Have you sorted the 'lookup' rows or columns by the value in the first row or column? If not, the LOOKUP won't always work.

Posted by Dave Hawley on January 09, 2001 10:48 PM

Hi Tim

You may want to consider using a combonation of INDEX and MATCH like:
=INDEX(A1:A100,MATCH("abe",A1:A100,0),,1)


In this case MATCH is returning the row number to INDEX. the zero is telling MATCH to return the row number for an exact match. And 1 is the Column number.

Dave


OzGrid Business Applications

Posted by Martin Badsted on January 11, 2001 12:24 AM

If the data isn't sorted it's easier to use the VLOOKUP or HLOOKUP functions. The syntax is:

VLOOKUP(lookup_value,table_array, col_index_num,range_lookup)

If the "range_lookup" is FALSE the data doesn't need to be sorted.

Posted by Tim Pedersen on January 12, 2001 6:21 AM


You guys are great - this is the first time I've posted a question here, and your help has been fantastic. Thanks!