MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLookup - I just can't see the problem


Posted by Phil D. Gonzalez on November 15, 2001 3:03 PM

I apologize profusely in advance for the length of this message. I saw no way of reducing this and still be able to explain my problem.

VLookup is failing for me in the following scenario (please bear with me, this will be long):

Workbook 1 has 13 worksheets named Jan, Feb, ... Dec, and DataList (basically, 1 sheet named for each month, and an extra sheet named DataList).

All 13 worksheets in Workbook 1 has 4 columns (Date, Client, Order Number, Amount), with respective data in each column. The idea is that the end-user chooses the appropriate worksheet for the month and enters in the necessary information (Order number, date, etc).

The worksheet named DataList has the same 4 columns, but in a different order (Order Number, Date, Client, Amount). The following formulas were entered on the first row of each column:

Order Number Column has =IF(ISBLANK(Jan!c2),"",Jan!c2)
Date Column has =IF(ISBLANK(Jan!a2),"",Jan!a2)
Client Column has =IF(ISBLANK(Jan!b2),"",Jan!b2)
Amount Column has =IF(ISBLANK(Jan!d2),"",Jan!d2)

These formulas were then dragged/filled down to row 100, whereupon the worksheet/cell references were changed to Feb!c2, Feb!a2, etc and dragged down for another 100 rows. So essentially we ended up with 1200 rows of these formulas in the DataList worksheet.

The assumption here is that there would never be more than 100 orders in any given month. Furthermore, during the course of the year, some months would have less orders than others. The DataList worksheet would then have "gaps" in its listing of orders (eg. Rows 2-30 would list the 29 orders entered in Jan, rows 31-101 would be blank, then rows 102-110 would list the 9 orders entered in Feb, etc).

Here's where the VLookup issue comes up (finally!).

There is a Workbook 2 which has 3 columns (named Date, Client, and Order Number). The following formulas were entered on the first row of each column:

Date Column has =IF(ISBLANK(C2)," ",VLOOKUP($C2,'[ORDERS.XLS]DataList'!$A$2:$D$1201,2,FALSE))

Client Column has =IF(ISBLANK(C2)," ",VLOOKUP($C2,'[ORDERS.XLS]DataList'!$A$2:$D$1201,3,FALSE))

Order Number Column is blank.

The way it's supposed to work is that the end user enters the Order Number in the appropriate column and the Date and Client name cells are filled in.

This whole setup works quite well (even though I'm sure there's a cleaner way to do it) but only for the data entered in the Jan worksheet. Once the first range of blank cells is reached, VLOOKUP returns N/A for all orders numbers entered past that point. I cannot see why this is so.

Any ideas out there?


Posted by Richard S on November 15, 2001 3:56 PM

Instead of your formula entering a "" if there is nothing, try 0. Just a thought. Not sure if it will work as I haven't tried it. Let me know what happens
Richard

Posted by Catherine Munro on November 15, 2001 4:42 PM

=INDEX('[ORDERS.XLS]DataList'!$A$2:$D$1201,MATCH($C2,'[ORDERS.XLS]DataList'!$A$2:$A$1201,FALSE),2)

Vlookup requires that the lookup range be sorted ascending; as soon as it finds an entry larger than your desired lookup value, it stops searching. Therefore the blank rows in your table are throwing it off.

My solution is to use Index & Match functions together.

Match returns the row number of a lookup value from a one-column lookup table:
=MATCH(lookup_value,lookup_array,match_type)
=MATCH($C2,'[ORDERS.XLS]DataList'!$A$2:$A$1201,FALSE)

Index works like Vlookup, but needn't be sorted:
=INDEX(reference,row_num,column_num)
where reference = full lookup table,
row_num = uses results of MATCH formula
col_num = self-explanatory

Thus:
=INDEX('[ORDERS.XLS]DataList'!$A$2:$D$1201,MATCH($C2,'[ORDERS.XLS]DataList'!$A$2:$A$1201,FALSE),2)

Note that the number of rows in MATCH "lookup_array" and INDEX "reference" *must* be equal, and must start with same row number. Oh, and there's two different syntaxes for INDEX -- you'll usually want the 'reference' version for this sort of thing, not 'array'.

It's not as confusing as it looks. Use Excel help and the Formula Palette (the "=" button) if you get stuck.

Hope that helps!
Catherine