VLOOKUP + SHEETS

PM1

Board Regular
Joined
Oct 28, 2005
Messages
192
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

PM1

Board Regular
Joined
Oct 28, 2005
Messages
192
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
21LN061888J0E5KYD32WL99457L20002WindowsNTServersS171A26/07/2004292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
32LN061889J0BTKYD32WL99457L20002WindowsNTServersS171A26/07/2004292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
43LN061890J0CXKYD32WL99457L20002WindowsNTServersS171A26/07/2004292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
54LN061891J0DDKYD32WL99457L20002WindowsNTServersS171A26/07/2004292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
65LN061892J0CUKYD32WL99457L20002WindowsNTServersS171A26/07/2004292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
76LN061893J0E1KYD32WL99457L20002WindowsNTServersS171A26/07/2004292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
87L99422L20012WindowsNTServersS357A14/07/2004325134-421ProLiantDL580(G2)R02Xeon
98L99411L20004HardwareMaintenanceS289A15/07/2004FAS960C-IB-BASE2FAS960,CFO,IB,DATAONTAP
109L99422L20012IBMServersS229K30/07/20047038-6M2RackServer1:pSeries650Model6M2CEC
1110L99422L20012IBMServersS229K30/07/20047038-6M2RackServer1:pSeries650Model6M2CEC
1211L99422L20012IBMServersS229K30/07/20047038-6M2RackServer1:pSeries650Model6M2CEC
1312L99422L20012IBMServersS229K30/07/20047038-6M2RackServer1:pSeries650Model6M2CEC
1413L99422L20012IBMServersS229K30/07/20047311-D20I/ODrawer1:ModelD20I/ODrawer
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.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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)))
 

PM1

Board Regular
Joined
Oct 28, 2005
Messages
192
VIP u surely are :p

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

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,032
Messages
5,569,767
Members
412,291
Latest member
marypolitan
Top