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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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!!!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top