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?