# VLOOKUP + SHEETS

#### PM1

##### Board Regular
Can you do a VLookup in Multiple sheets.

I have a sheet with a column of unique No's. I now want to look up that No in another sheet and report.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
PM1 said:
Can you do a VLookup in Multiple sheets.

I have a sheet with a column of unique No's. I now want to look up that No in another sheet and report.

Why such a minimal specification? You can at least enumerate the names of the sheets, specify the "column of unique No's", and specify the ranges "in another sheet" that house the data.

OK, I will expand.

Sheet's 1 - 5 record assets from various locations within the company, like so
Master Asset File.xls
ABCDEFGHIJK
1AssetNumberLNnumberSERIALNUMBERSCostCentreBPiDeptCodeAssetclassSNo.EvalNo.ProjectCap.DateText
98L99411L20004HardwareMaintenanceS289A15/07/2004FAS960C-IB-BASE2FAS960,CFO,IB,DATAONTAP
BP²I Assets

The only unique item is the Asset No in Col A:

I have created another sheet where I have numbered Col "A" from 1 - 30000.

I know want to lookup the Val in Col A, ie No 1 from within the other 5 sheets and bring in the data that I want from the relative column. Each of the sheets are exactly the same as far as col headings are concerned.

Hi,

If you expect your VLOOKUP to return text, try:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4,5},VLOOKUP(A1,Sheet1!A2:B28,2,0),VLOOKUP(A1,Sheet2!A2:B28,2,0),VLOOKUP(A1,Sheet3!A2:B28,2,0),VLOOKUP(A1,Sheet4!A2:B28,2,0),VLOOKUP(A1,Sheet5!A2:B28,2,0)))

If you expect it to return a number:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5},VLOOKUP(A1,Sheet1!A2:B28,2,0),VLOOKUP(A1,Sheet2!A2:B28,2,0),VLOOKUP(A1,Sheet3!A2:B28,2,0),VLOOKUP(A1,Sheet4!A2:B28,2,0),VLOOKUP(A1,Sheet5!A2:B28,2,0)))

VIP u surely are

I understand the vlookup's but what does (REPT("z",255),CHOOSE({1,2,3,4,5}, or (9.99999999999999E+307,CHOOSE({1,2,3,4,5} mean ...?

The

CHOOSE({1,2,3,4,5}....

Makes an array of the result of the different VLOOKUP's such as

#N/A,#N/A,#N/A,123,#N/A

A LOOKUP with a number that is bigger than any number in the array e.g. 9.99999999999999E+307
will return the last( rightmost) number in the array.

Same principal goes for text

REPT("z",255)

generates a text string, larger than any that could appear in the array.

Replies
1
Views
575
Replies
1
Views
266
Replies
2
Views
108
Replies
0
Views
421
Replies
4
Views
356

1,212,141
Messages
6,106,204
Members
448,005
Latest member
Valvictor

### 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.

### Which adblocker are you using?

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

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