Shorten nest iferror(vlookup(

[DataBlake]

So i have a nested formula that i would like to shorten or to convert to VBA

General info:
- I have a sheet containing part numbers in column A: sheet name "Unknown"
- I have 3 sheets containing information i need each with different part numbers. sheet names: 1,2,3
One of the 3 sheets contains the part number from "Unknown"
- I have a vlookup that looks in each sheet and then grabs the information i need

Problems:
- I have to add sheets in the future so i will have to go back and nest even more statements every time i add a sheet (4,5,6,7,etc)
- I will eventually hit a wall of maximum nested statements
- Its not pretty

=IF(Unknown!B2="new","1 New "&IFERROR(VLOOKUP(Unknown!A2,'1'!1:1048576,3,FALSE),IFERROR(VLOOKUP(Unknown!A2,'2'!1:1048576,3,FALSE),IFERROR(VLOOKUP(Unknown!A2,'3'!1:1048576,3,FALSE)&" "&IFERROR(VLOOKUP(Unknown!A2,'1'!1:1048576,4,FALSE),IFERROR(VLOOKUP(Unknown!A2,'2'!1:1048576,4,FALSE),IFERROR(VLOOKUP(Unknown!A2,'3'!1:1048576,4,FALSE),.......""))),""))))

how can i shorten this or turn it into a VBA macro that will sequence down a column until the end of a row?


ideas:
VBA using the sheetnames as a variant to create an array to search for the partnumber. Then use that row to grab the info from each column i need.

 

[Jeffrey Mahoney]

Using a VBA User Defined Function for that many formulas is going to be slow. If you just need the values and not the formulas, that would work.

Please accept these suggestions. You're Vlookup table is basically every row and column on the Sheets '1', '2', and '3'. I'm not exactly sure about how much time it would save, but it might be better to address a smaller table. I could show you how to create a dynamic named range for that. Can you use IFERROR once at the beginning? It looks like you have them in the wrong place, for instance:

This:
=IF(Unknown!B2="new","1 New "&IFERROR(VLOOKUP(Unknown!A2,'1'!1:1048576,3,FALSE), Other stuff here ,"")
should be this:
=IF(Unknown!B2="new","1 New "&IFERROR(VLOOKUP(Unknown!A2,'1'!1:1048576,3,FALSE),""), Other Stuff Here

Your first IFERROR ends at the end of the whole formula.

My 2 cents

Jeff

 

[DataBlake]

Using a VBA User Defined Function for that many formulas is going to be slow. If you just need the values and not the formulas, that would work.

Please accept these suggestions. You're Vlookup table is basically every row and column on the Sheets '1', '2', and '3'. I'm not exactly sure about how much time it would save, but it might be better to address a smaller table. I could show you how to create a dynamic named range for that. Can you use IFERROR once at the beginning? It looks like you have them in the wrong place, for instance:

This:
=IF(Unknown!B2="new","1 New "&IFERROR(VLOOKUP(Unknown!A2,'1'!1:1048576,3,FALSE), Other stuff here ,"")
should be this:
=IF(Unknown!B2="new","1 New "&IFERROR(VLOOKUP(Unknown!A2,'1'!1:1048576,3,FALSE),""), Other Stuff Here

Your first IFERROR ends at the end of the whole formula.

My 2 cents

Jeff

yes i did fix that to be this. this is the formula after i finished it & works for 3 sheets. The problems are still glaring at me and i'd like to solve the issues i will run into before i run into them.

=IF(Unknown!B3="new","1 New "&IFERROR(VLOOKUP(Unknown!A3,Sheet1!$1:$1048576,3,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet2!$1:$1048576,3,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet3!$1:$1048576,3,FALSE),"")))&" ET "&IFERROR(VLOOKUP(Unknown!A3,Sheet1!$1:$1048576,7,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet2!$1:$1048576,7,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet3!$1:$1048576,7,FALSE),"")))&" "&IFERROR(VLOOKUP(Unknown!A3,Sheet1!$1:$1048576,12,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet2!$1:$1048576,12,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet3!$1:$1048576,12,FALSE),"")))&" "&IFERROR(VLOOKUP(Unknown!A3,Sheet1!$1:$1048576,10,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet2!$1:$1048576,10,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet3!$1:$1048576,10,FALSE),"")))&" "&IFERROR(VLOOKUP(Unknown!A3,Sheet1!$1:$1048576,4,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet2!$1:$1048576,4,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet3!$1:$1048576,4,FALSE),""))))&" Wheel "&IFERROR(VLOOKUP(Unknown!A3,Sheet1!$1:$1048576,6,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet2!$1:$1048576,6,FALSE),IFERROR(VLOOKUP(Unknown!A3,Sheet3!$1:$1048576,6,FALSE),"")))

and maybe i should explain more?
there are over 100,000 part numbers i will be using this code on so VBA (even if its slow) would be great in the long run. I will still have to go back and add 4 lines of nested if statements the next time i add a sheet of part numbers. i would much rather go into vba and add a sheet name into a dim variant or array or something. I'm just so lost on the syntax part of VBA, but i have the idea. I believe you can only have 250 nested if statements? I will hit that in no time.

the logic of how this works is
Column A of sheet "Unknown" contains any number of part numbers
Somewhere in Sheet1-3 is the part number also in column A
once it finds that part number i need it to grab the information from the same row in columns: 3,7,12,10,4 in that order.
im almost certain this is doable in VBA i just don't know how.

 

[Jeffrey Mahoney]

I have an idea. Your main issue is to find which sheet the part is on so you can retrieve the data. So, if we use a cell in each row to just give us the sheet name, then we can simply the formula using the INDIRECT function. It would simplify things a lot.

To find the sheet name where the part is (I put this in column B):
=IF(IFERROR(MATCH(A3,Sheet1!A:A,0),0)>0,"Sheet1",IF(IFERROR(MATCH(Unknown!A3,Sheet2!A:A,0),0)>0,"Sheet2",IF(IFERROR(MATCH(Unknown!A3,Sheet3!A:A,0),0)>0,"Sheet3","")))

Anywhere in your formula that uses this VLOOKUP (or something similar):
VLOOKUP(Unknown!A3,Sheet1!$1:$1048576,3,FALSE)

Replace it with this:
VLOOKUP(Unknown!A3,INDIRECT("'"&B3&"'!1:1048576"),3,FALSE)


This prevents you from having to test by using vlookups. The formula to get the sheet name is easily expandable to include other sheets.

What do you think?

Jeff

 

[DataBlake]

I have an idea. Your main issue is to find which sheet the part is on so you can retrieve the data. So, if we use a cell in each row to just give us the sheet name, then we can simply the formula using the INDIRECT function. It would simplify things a lot.

To find the sheet name where the part is (I put this in column B):
=IF(IFERROR(MATCH(A3,Sheet1!A:A,0),0)>0,"Sheet1",IF(IFERROR(MATCH(Unknown!A3,Sheet2!A:A,0),0)>0,"Sheet2",IF(IFERROR(MATCH(Unknown!A3,Sheet3!A:A,0),0)>0,"Sheet3","")))

Anywhere in your formula that uses this VLOOKUP (or something similar):
VLOOKUP(Unknown!A3,Sheet1!$1:$1048576,3,FALSE)

Replace it with this:
VLOOKUP(Unknown!A3,INDIRECT("'"&B3&"'!1:1048576"),3,FALSE)


This prevents you from having to test by using vlookups. The formula to get the sheet name is easily expandable to include other sheets.

What do you think?

Jeff

I appreciate your response, but it seems i am just SUPER dumb
i just created a helper column addon in VBA that inserts a column (in sheets 1-3) and combines the data (using =Concatenate) i need so that i can grab it using vlookup.
This is a lot simpler than using nested vlookups to grab the data individually.