complex vlookup

blakrapter

New Member
Joined
Oct 16, 2006
Messages
13
Hello again,

I have another question for you guys. I am working with a spreadsheet that uses a lot of vlookup functions to pull data from various workbooks. The data is a price that is linked to the product ID. Some manufacturers give me the min price I can sell for, so basically, this is what I have in each price cell on the main price list (about 2,200 lines):

look for part number in workbook A, if available, then list price
if not, look for PN in workbook B, if available, then list price
if not, look for PN in workbook C, if available, then list price
if not, look for PN in workbook D, list price (has to be there)

The basic format I have is:

IF function looking at an ISNA function which calls a VLOOKUP if ISNA=0, else it goes to the next ISNA function until it finds one that =0. There are 4 of these functions stacked on top of each other this way, which gives a formula about 6 lines long and very inefficient since it updates about 2000 cells this way by pulling data from 3 lists with sizes ranging from 750 to 20,000 entries.

Here is the actual code:
Code:
=IF(ISNA(IF(ISNA(VLOOKUP(A1262,'WorkbookA'!B$1:G$700,6,FALSE)=TRUE), IF(ISNA(VLOOKUP(A1262,'WorkbookB'!B$1:G$700,6,FALSE)=TRUE), VLOOKUP(A1262,WorkbookC'!A$1:X$15000,22,FALSE), VLOOKUP(A1262,'WorkbookB'!B$1:G$700,6,FALSE)), VLOOKUP(A1262,'WorkbookA'!B$1:G$700,6,FALSE)))=TRUE,VLOOKUP(A1262,'WorkbookD'!A1262:S$15000,7,FALSE),IF(ISNA(VLOOKUP(A1262,'WorkbookA'!B$1:G$700,6,FALSE)=TRUE), IF(ISNA(VLOOKUP(A1262,'WorkbookB'!B$1:G$700,6,FALSE)=TRUE), VLOOKUP(A1262,WorkbookC'!A$1:Y$15000,22,FALSE), VLOOKUP(A1262,'WorkbookB'!B$1:G$700,6,FALSE)), VLOOKUP(A1262,'WorkbookA'!B$1:G$700,6,FALSE)))

When I update the price list (daily), it takes excel a good 1.5-2 minutes to update everything. Is there a way to do this more efficiently?

Thanks!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

guppas

New Member
Joined
Mar 26, 2004
Messages
30
I don't know about the length of time to calculate, but the code is very complex and difficult to read. I rarely do more than two lookups in a formula, prefering to do each into their own cells and then selecting one of the results.

In your example, this would, for instance, put the result of the workbook A lookup in column a, the result of the B lookup in column b, etc. Then another selection of "if"s can select one of the results.

You might try it just to see if it speeds the process up. Another idea might be to copy the tables to the workbook your formulas are in to see if the "across file" access is slowing it down.

I hope that helps!
 

sailepaty

Active Member
Joined
Nov 1, 2005
Messages
279
Give a try to this.

=IF(COUNTIF('WorkbookA'!B$1:B$700,A1262),VLOOKUP(A1262,'WorkbookA'!B$1:G$700,6,0),IF(COUNTIF('WorkbookB'!B$1:B$700,A1262),VLOOKUP(A1262,'WorkbookB'!B$1:G$700,6,0),IF(COUNTIF('WorkbookC'!A$1:A$15000,A1262),VLOOKUP(A1262,'WorkbookC'!A$1:X$15000,22,0),VLOOKUP(A1262,'WorkbookD'!A$1262:S$15000,7,0))))


I don’t know how much is going to increase the performance but at least is easier to read.

Regards.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,388
Maybe...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4},VLOOKUP(A1262,WorkbookD!A$1262:S$15000,7,FALSE),VLOOKUP(A1262,WorkbookC!A$1:Y$15000,22,FALSE),VLOOKUP(A1262,WorkbookB!B$1:G$700,6,FALSE),VLOOKUP(A1262,WorkbookA!B$1:G$700,6,FALSE)))

Hope this helps!
 

blakrapter

New Member
Joined
Oct 16, 2006
Messages
13

ADVERTISEMENT

Thank you for the info. I tried both new codes and they did not work. There is a reference problem I think. The A1262 reference is the problem. I am not sure exactly what each function is doing so I could not troubleshoot it. If possible, please give me a brief description of how the provided code works so I can troubleshoot it. I like the simplicity of the new codes, I just have to figure out how to make them work.

Thanks for the help!!!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,388
With regards to my formula, each VLOOKUP function returns a list price/number based on their respective lookup value. These numbers are returned as an array via the CHOOSE function. This array of numbers is then passed on to the LOOKUP function which returns the last numerical value. So, for example, if the last VLOOKUP function returns #N/A, the formula will return the result from the second last VLOOKUP function, and so on.
 

AussieJac

Board Regular
Joined
Jul 7, 2005
Messages
57

ADVERTISEMENT

You might want to investigate using INDEX & MATCH instead of Vlookup.
It works well with nested IF statements and ISNA
Check with Excel Help for a good example.
 

blakrapter

New Member
Joined
Oct 16, 2006
Messages
13
Aussie,

I checked on the index and match and couldn't get it to work. It appears that the exact cell locaiton of the necessary info must be known for that to work. Please correct me if I am wrong.

Domenic,
Your code worked great after I worked out protocall bugs. I looked up all of the functions and couldn't figure out how it worked though. It looks like the lookup function looks for the largest value in the array, not necessarily the first number when reading from right to left. That is the way it looks to me anyway. It is, however, functioning the way you say. While I don't need it for this code b/c it is working, can you elaborate on my it looks for the first number starting at the right for future reference?

Thanks!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,388
It looks like the lookup function looks for the largest value in the array, not necessarily the first number when reading from right to left.

If you test it you'll find that the LOOKUP function will indeed return the last numerical value in the array. Have a look a Aladin's detailed explanation here.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,403
Messages
5,547,750
Members
410,811
Latest member
adustin42
Top