Setting up a Vlookup from an array

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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
Code:
=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
Code:
=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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
could i do something like:

Code:
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

Code:
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
 
Upvote 0
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.
 
Upvote 0
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

Code:
=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.
 
Upvote 0
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
 
Upvote 0
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:

ABCD
1LookupSheet2Sheet3Sheet4
2A1
3F6
4R18
5L12

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B2=IFERROR( VLOOKUP( $A2, INDIRECT( B$1 & "!$A$2:$G$8"), 2, FALSE), "")
C2=IFERROR( VLOOKUP( $A2, INDIRECT( C$1 & "!$A$2:$G$8"), 2, FALSE), "")
D2=IFERROR( VLOOKUP( $A2, INDIRECT( D$1 & "!$A$2:$G$8"), 2, FALSE), "")
B3=IFERROR( VLOOKUP( $A3, INDIRECT( B$1 & "!$A$2:$G$8"), 2, FALSE), "")
C3=IFERROR( VLOOKUP( $A3, INDIRECT( C$1 & "!$A$2:$G$8"), 2, FALSE), "")
D3=IFERROR( VLOOKUP( $A3, INDIRECT( D$1 & "!$A$2:$G$8"), 2, FALSE), "")
B4=IFERROR( VLOOKUP( $A4, INDIRECT( B$1 & "!$A$2:$G$8"), 2, FALSE), "")
C4=IFERROR( VLOOKUP( $A4, INDIRECT( C$1 & "!$A$2:$G$8"), 2, FALSE), "")
D4=IFERROR( VLOOKUP( $A4, INDIRECT( D$1 & "!$A$2:$G$8"), 2, FALSE), "")
B5=IFERROR( VLOOKUP( $A5, INDIRECT( B$1 & "!$A$2:$G$8"), 2, FALSE), "")
C5=IFERROR( VLOOKUP( $A5, INDIRECT( C$1 & "!$A$2:$G$8"), 2, FALSE), "")
D5=IFERROR( VLOOKUP( $A5, INDIRECT( D$1 & "!$A$2:$G$8"), 2, FALSE), "")

<tbody>
</tbody>

<tbody>
</tbody>

This was on a series of sheets: Sheet2, Sheet3 and Sheet4, which contained sample data such as this:
AB
1CodeValue
2A1
3B2
4C3
5D4
6E5
7F6
8G7

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
 
Last edited:
Upvote 0
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"

Code:
=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
 
Upvote 0
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
EventCustom P#TitleSize
Add=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")
=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")
=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")

<tbody>
</tbody>

This is sheet "Unknown"

P#Status
1235new
1452new

<tbody>
</tbody>

Here is Sheet 3 which contain "1235"

p#TitleSizeDiameterSpecialColorVariant
1235blah blah4x104nobluemrexcel
7856blahblah3x103yesnotbluemrexcel
9854bla blah bla3x103yesblue2mrexcel
1210blaaaaaaah5x105nodef bluemrexcel

<tbody>
</tbody>

And here is Sheet 1 which contains "1452"

p#TitleDimensionsDiameteryes/noFinishStyle
1277blah bla4x74noredblakeskate
8888blah14x1014yesnotblueblakeskate
1452bla blah blaah3x113yesmagentablakeskate
9900bl ah5x75noother redblakeskate

<tbody>
</tbody>
 
Last edited:
Upvote 0
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

Sheets 1-6 (from lookup)
SheetInfo (to)
AB & L
PE
BK
EM
FN
HO
IP
JQ
KR
OY
QAA
LAD
RAF

<tbody>
</tbody>

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

Code:
=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")
 
Last edited:
Upvote 0
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 :
Code:
[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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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