VLOOKUP + SHEETS

[PM1]

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.

 

[Aladin Akyurek]

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]

OK, I will expand.

Sheet's 1 - 5 record assets from various locations within the company, like so

Master Asset File.xls
	A	B	C	D	E	F	G	H	I	J	K
1	AssetNumber	LNnumber	SERIALNUMBERS	CostCentre	BPiDeptCode	Assetclass	SNo.	EvalNo.	Project	Cap.Date	Text
2	1	LN061888	J0E5KYD32W	L99457	L20002	WindowsNTServers			S171A	26/07/2004	292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
3	2	LN061889	J0BTKYD32W	L99457	L20002	WindowsNTServers			S171A	26/07/2004	292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
4	3	LN061890	J0CXKYD32W	L99457	L20002	WindowsNTServers			S171A	26/07/2004	292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
5	4	LN061891	J0DDKYD32W	L99457	L20002	WindowsNTServers			S171A	26/07/2004	292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
6	5	LN061892	J0CUKYD32W	L99457	L20002	WindowsNTServers			S171A	26/07/2004	292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
7	6	LN061893	J0E1KYD32W	L99457	L20002	WindowsNTServers			S171A	26/07/2004	292889-421ProLiantDL360(G3)R02XeonDP2.8GHz,512MB
8	7			L99422	L20012	WindowsNTServers			S357A	14/07/2004	325134-421ProLiantDL580(G2)R02Xeon
9	8			L99411	L20004	HardwareMaintenance			S289A	15/07/2004	FAS960C-IB-BASE2FAS960,CFO,IB,DATAONTAP
10	9			L99422	L20012	IBMServers			S229K	30/07/2004	7038-6M2RackServer1:pSeries650Model6M2CEC
11	10			L99422	L20012	IBMServers			S229K	30/07/2004	7038-6M2RackServer1:pSeries650Model6M2CEC
12	11			L99422	L20012	IBMServers			S229K	30/07/2004	7038-6M2RackServer1:pSeries650Model6M2CEC
13	12			L99422	L20012	IBMServers			S229K	30/07/2004	7038-6M2RackServer1:pSeries650Model6M2CEC
14	13			L99422	L20012	IBMServers			S229K	30/07/2004	7311-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]

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]

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

 

[fairwinds]

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.