Setting up a Vlookup from an array

[DataBlake]

Hi all,
So i have 6 sheets (and growing) that i have multiple vlookups that reference all 6 sheets an example of this would be

=IF(Unknown!B2="new",IFERROR(VLOOKUP(Unknown!A2,Sheet1!$1:$1048576,1,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet2!$1:$1048576,1,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet3!$1:$1048576,1,FALSE),
IFERROR(VLOOKUP(Unknown!A2,Sheet4!$1:$1048576,1,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet5!$1:$1048576,1,FALSE),"err"))))),"err")

I would instead like it to be

=IF(Unknown!B2="new",IFERROR(VLOOKUP(Unknown!A2,SHEETARRAY...)

I am unsure of how to make an array/name for all 6 sheets and get the vlookup to reference said array/name.
If its possible it will eliminate the nested if statements that will eventually reach their limit as well as causing less user entered mistakes.

if thats only possible through VBA thats more than fine with me.

Any help would be greatly appreciated

 

[DataBlake]

could i do something like:

SheetNames = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")


Range("B2").Select
    ActiveCell.FormulaR1C1 = _
 "=IF(Unknown!RC=""new"",IFERROR(VLOOKUP(Unknown!RC[-1]," & SheetNames & "!R1:R1048576,1,FALSE),"err"),"err")"

or would i have to use

For Each sheetname In SheetNames
x + 1
Next sheetname

and then replace sheetnames with just the number placement of the sheet?
like lets say sheets 1-6 are the first through the sixth sheets
i feel like i'm awfully close, but i'm missing something

 

[BlueHornet]

There is a way to do this, though it may not seem to be as clean as you would like - and it's not contained in a single-cell formula.

Let's say that Sheet1 contains the value to be looked up and places for the return to appear. Sheet2, Sheet3 and Sheet4 contain the lookup tables in A2:G7 on each worksheet. Sheet1 also contains headers with the exact names of each of the Lookup worksheets: Sheet2, Sheet3, Sheet4, all lined up at B1:D1.

At B2, enter this formula: =IFERROR( VLOOKUP( $A2,INDIRECT(B$1 & "!$A$2:$G$7"), 2, FALSE), "") and copy it across all of the columns of potential worksheets and down through all of the rows that may have Lookup request items. Your return value/s will be arrayed across the sheet, which is obviously expandable as your collection of worksheets grows.

 

[DataBlake]

Possibly just array?

There is a way to do this, though it may not seem to be as clean as you would like - and it's not contained in a single-cell formula.

Let's say that Sheet1 contains the value to be looked up and places for the return to appear. Sheet2, Sheet3 and Sheet4 contain the lookup tables in A2:G7 on each worksheet. Sheet1 also contains headers with the exact names of each of the Lookup worksheets: Sheet2, Sheet3, Sheet4, all lined up at B1:D1.

At B2, enter this formula: =IFERROR( VLOOKUP( $A2,INDIRECT(B$1 & "!$A$2:$G$7"), 2, FALSE), "") and copy it across all of the columns of potential worksheets and down through all of the rows that may have Lookup request items. Your return value/s will be arrayed across the sheet, which is obviously expandable as your collection of worksheets grows.

I'm a little lost. Maybe i can elaborate more.
Sheets 1-6 are all sheets i get from various vendors that each have their own organization
I already have a macro set to organize this data to be all the same so say for example the size of something is located in column G after i run a macro
I have nested iferror lookup formulas that will find this information within the 6 sheets of vendor data
So it looks up a part number in all 6 of my vendors to grab the size for one item

=IF(Unknown!B2="new",IFERROR(VLOOKUP(Unknown!A2,Sheet1!$1:$1048576,6,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet2!$1:$1048576,6,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet3!$1:$1048576,6,FALSE), IFERROR(VLOOKUP(Unknown!A2,Sheet4!$1:$1048576,6,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet5!$1:$1048576,6,FALSE),"err"))))),"err")

I would like to instead replace this formula with one that is smaller and cleaner / have VBA produce an optimal vlookup
OR
possibly remove the vlookup all together and go with variant arrays or match
I just don't have the know how to create such things

adding helper columns, tables, and more formulas is not something i want to do as this workbook is already at 19MB
besides replacing one complicated thing with another complicated thing isn't what i want either so i can reduce user error when using the workbook.

 

[offthelip]

Re: Possibly just array?

Can you describe what you are trying to do? I never use vlookup in VBA since using Variant arrays is faster and easier to my way of thinking I don't feel like trying to work out what you are trying to do from your vlookups

 

[BlueHornet]

Re: Possibly just array?

I didn't think the description I gave would be difficult to follow. All it takes is a single formula, copied over all of the columns that would contain different sheet names:

	A	B	C	D
Lookup	Sheet2	Sheet3	Sheet4
A
F
R
L

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]12[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A2, INDIRECT( B$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A2, INDIRECT( C$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A2, INDIRECT( D$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]B3[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A3, INDIRECT( B$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]C3[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A3, INDIRECT( C$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A3, INDIRECT( D$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]B4[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A4, INDIRECT( B$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]C4[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A4, INDIRECT( C$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A4, INDIRECT( D$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]B5[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A5, INDIRECT( B$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]C5[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A5, INDIRECT( C$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=IFERROR( VLOOKUP( $A5, INDIRECT( D$1 & "!$A$2:$G$8"), 2, FALSE), "")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This was on a series of sheets: Sheet2, Sheet3 and Sheet4, which contained sample data such as this:

	A	B
Code	Value
A
B
C
D
E
F
G

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]

</tbody>

Sheet2​

 

[DataBlake]

Re: Possibly just array?

Can you describe what you are trying to do? I never use vlookup in VBA since using Variant arrays is faster and easier to my way of thinking I don't feel like trying to work out what you are trying to do from your vlookups

absolutely
I have a sheet "SheetInfo"
where it checks a status (new or old) in Sheet "Unknown" Column B & if the value of B is "new" it will look up the part number (in Sheet Unknown column A) within several sheets in the workbook
Sheets 1-6 contain information for the part number such as size (column G) and weight (column N)
So if the item number is new it will grab this information and place it in specific cellcs on "SheetInfo"

So right now i have a nested vlookup to do the above (not in VBA)
if its not new it returns "err"

=IF(Unknown!B2="new",IFERROR(VLOOKUP(Unknown!A2,Sheet1!$1:$1048576,1,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet2!$1:$1048576,1,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet3!$1:$1048576,1,FALSE),
IFERROR(VLOOKUP(Unknown!A2,Sheet4!$1:$1048576,1,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet5!$1:$1048576,1,FALSE),"err"))))),"err")

I'm essentially trying to replace these vlookups with a macro or simpler solution so that im not constantly adding to the vlookup whenever i add a sheet (like sheet 7)
if theres a way to just add the sheet name and change which values it returns from the lookup/match/variant array i'll be able to save a lot of user error and time.
thats pretty much the best i can explain it, but i can always upload a version of it

 

[DataBlake]

Re: Possibly just array?

I didn't think the description I gave would be difficult to follow. All it takes is a single formula, copied over all of the columns that would contain different sheet names

I get what this does now, thank you.
So wouldn't i have to have the sheet names as a header for each value i want to return? Like if i wanted to find the size for part number 125 i would have to have 6 columns to find one size.
As a helper sheet this works, but i already have the part numbers and info within the sheets 1-6
I'm not trying to identify which sheet it is, i'm just trying to do the equivalent of a vlookup in 6 sheets in one cell.
Sorry for the confusion. I must not have explained well enough.

So here is the sheet i'm trying to add information to
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Event[/TD]
[TD]Custom P#[/TD]
[TD]Title[/TD]
[TD]Size[/TD]
[/TR]
[TR]
[TD]Add[/TD]
[TD]=IF(Unknown!B2="new",IFERROR(VLOOKUP(Unknown!A2,Sheet1!$1:$1048576,1,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet2!$1:$1048576,1,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet3!$1:$1048576,1,FALSE),
IFERROR(VLOOKUP(Unknown!A2,Sheet4!$1:$1048576,1,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet5!$1:$1048576,1,FALSE),"err"))))),"err")[/TD]
[TD]=IF(Unknown!B2="new",IFERROR(VLOOKUP(Unknown!A2,Sheet1!$1:$1048576,2,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet2!$1:$1048576,2,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet3!$1:$1048576,2,FALSE),
IFERROR(VLOOKUP(Unknown!A2,Sheet4!$1:$1048576,2,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet5!$1:$1048576,2,FALSE),"err"))))),"err")[/TD]
[TD]=IF(Unknown!B2="new",IFERROR(VLOOKUP(Unknown!A2,Sheet1!$1:$1048576,3,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet2!$1:$1048576,3,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet3!$1:$1048576,3,FALSE),
IFERROR(VLOOKUP(Unknown!A2,Sheet4!$1:$1048576,3,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet5!$1:$1048576,3,FALSE),"err"))))),"err")[/TD]
[/TR]
</tbody>[/TABLE]

This is sheet "Unknown"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]P#[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]new[/TD]
[/TR]
[TR]
[TD]1452[/TD]
[TD]new[/TD]
[/TR]
</tbody>[/TABLE]

Here is Sheet 3 which contain "1235"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]p#[/TD]
[TD]Title[/TD]
[TD]Size[/TD]
[TD]Diameter[/TD]
[TD]Special[/TD]
[TD]Color[/TD]
[TD]Variant[/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]blah blah[/TD]
[TD]4x10[/TD]
[TD]4[/TD]
[TD]no[/TD]
[TD]blue[/TD]
[TD]mrexcel[/TD]
[/TR]
[TR]
[TD]7856[/TD]
[TD]blahblah[/TD]
[TD]3x10[/TD]
[TD]3[/TD]
[TD]yes[/TD]
[TD]notblue[/TD]
[TD]mrexcel[/TD]
[/TR]
[TR]
[TD]9854[/TD]
[TD]bla blah bla[/TD]
[TD]3x10[/TD]
[TD]3[/TD]
[TD]yes[/TD]
[TD]blue2[/TD]
[TD]mrexcel[/TD]
[/TR]
[TR]
[TD]1210[/TD]
[TD]blaaaaaaah[/TD]
[TD]5x10[/TD]
[TD]5[/TD]
[TD]no[/TD]
[TD]def blue[/TD]
[TD]mrexcel[/TD]
[/TR]
</tbody>[/TABLE]

And here is Sheet 1 which contains "1452"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]p#[/TD]
[TD]Title[/TD]
[TD]Dimensions[/TD]
[TD]Diameter[/TD]
[TD]yes/no[/TD]
[TD]Finish[/TD]
[TD]Style[/TD]
[/TR]
[TR]
[TD]1277[/TD]
[TD]blah bla[/TD]
[TD]4x7[/TD]
[TD]4[/TD]
[TD]no[/TD]
[TD]red[/TD]
[TD]blakeskate[/TD]
[/TR]
[TR]
[TD]8888[/TD]
[TD]blah[/TD]
[TD]14x10[/TD]
[TD]14[/TD]
[TD]yes[/TD]
[TD]notblue[/TD]
[TD]blakeskate[/TD]
[/TR]
[TR]
[TD]1452[/TD]
[TD]bla blah blaah[/TD]
[TD]3x11[/TD]
[TD]3[/TD]
[TD]yes[/TD]
[TD]magenta[/TD]
[TD]blakeskate[/TD]
[/TR]
[TR]
[TD]9900[/TD]
[TD]bl ah[/TD]
[TD]5x7[/TD]
[TD]5[/TD]
[TD]no[/TD]
[TD]other red[/TD]
[TD]blakeskate[/TD]
[/TR]
</tbody>[/TABLE]

 

[DataBlake]

Re: Possibly just array?

and @offthelip
just in case you need me to get hyper specific here is a list of values and where they need to go in regards to columns

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sheets 1-6 (from lookup)
[/TD]
[TD]SheetInfo (to)
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B & L[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]O[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]AF[/TD]
[/TR]
</tbody>[/TABLE]

So an example of how i have it right now
for cell AA2 on sheetinfo:

=IF(Unknown!B2="new",IFERROR(VLOOKUP(Unknown!A2,Sheet1!$1:$1048576,17,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet2!$1:$1048576,17,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet3!$1:$1048576,17,FALSE),
IFERROR(VLOOKUP(Unknown!A2,Sheet4!$1:$1048576,17,FALSE),IFERROR(VLOOKUP(Unknown!A2,Sheet5!$1:$1048576,17,FALSE),"err"))))),"err")

 

[offthelip]

Re: Possibly just array?

Sheets 1-6 contain information for the part number such as size (column G) and weight (column N)

which column in sheets 1 to 6 is the part number you are trying to match in, or could it be in any column?
Is there only one match for the partnumber or could there be matches on more than one sheet?
Do you want the search to all sheets apart from the "sheetinfo" sheet in the workbook.?
How many rows on the sheetinfo worksheet do you want matches donw ( all rows with data in column A?)
Do you really need to search 1048576 rows?
The way I would do this is to load every sheet in the workbook into variant arrays , then copy these into a threee dimension variant array, and just loop through all three dimensions to do the matches
since I don't use vlookup I don't know what :

[COLOR=#333333]VLOOKUP(Unknown!A2,Sheet2!$1:$1048576,17,FALSE)[/COLOR]

does without looking through the documentation. Is this looking at all columns on sheet2?