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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,116
Messages
5,835,476
Members
430,358
Latest member
zzc1128

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
Top