VLOOKUP across 6 tables

[thewiseguy]

I am having trouble with this formula. Any help is appreciated:

A VLOOKUP across 6 tables. Here is my formula but saying too many arguments:

=IFERROR(VLOOKUP(A2,Table_Fixtures,5,0),VLOOKUP(A2,Table_Lamps,4,0),VLOOKUP(A2,Table_Accessory,4,0),VLOOKUP(A2,Table_Controls,3,0),VLOOKUP(A2,Table_Materials,4,0),VLOOKUP(A2,Table_Rental,4,0))))))))

 

[awoohaw]

Is the lookup value mutually exclusive from the other tables?
Here is my attempt, using VSTACK to create a single lookup range.
But if the value in A2 is not exclusive to one table you will not get all the results you seek.

Mr excel questions 53.xlsm
	A	B	C	D	E	F	G	H	I	J
1	3
2	B
3
4	Table_Fixtures						Table_Controls
5	Col1	Col2	Col3	Col4			Col1	Col2	Col3	Col4
6	A	4	5	6			A	8	9	9
7	B	1	6	3			B	5	8	7
8
9
10	Table_Lamps						Table_Materials
11	Col1	Col2	Col3	Col4			Col1	Col2	Col3	Col4
12	A	6	7	9			A	8	5	7
13	B	9	0	2			B	8	7	7
14
15
16	Table_accessory						Table_Rental
17	Col1	Col2	Col3	Col4			Col1	Col2	Col3	Col4
18	A	7	9	6			A	7	5	5
19	B	5	9	7			B	6	7	8
thewiseguy

Cell Formulas
Range	Formula
A1	A1	=IFERROR(VLOOKUP(A2,VSTACK(Table_Fixtures,Table_Lamps,Table_accessory,Table_Controls,Table_Materials,Table_Rental),4,0),"")

 

[thewiseguy]

the VLOOKUP is supposed to look over 6 different tables. i tried this with the VSTACK function but i was only getting the proper results from column A. none of the other columns were populating correctly.

 

[Anonymous1378]

The return column numbers of all your tables are different, do they at least have the same header or something?

 

[awoohaw]

the VLOOKUP is supposed to look over 6 different tables. i tried this with the VSTACK function but i was only getting the proper results from column A. none of the other columns were populating correctly.

I do not understand what you mean. Mr Excel has a tool called xl2bb add in (link below) where you can share a mini worksheet.

As far as VSTACK, in the below image I have VSTACKed all 6 table. I changed Header column data to demonstrate that the VSTACK reads all the tables.
And as you can see the syntax i've used is also functional.

If this is not what you expect, please explain in detail what you want and give some examples.

Mr excel questions 53.xlsm
	A	B	C	D	E	F	G	H	I	J
1	7
2	H
3
4	=VSTACK(Table_Fixtures,Table_Lamps,Table_accessory,Table_Controls,Table_Materials,Table_Rental)
5	A	4	5	6
6	B	1	6	3
7	C	6	7	9
8	D	9	0	2
9	D	7	9	6
10	E	5	9	7
11	F	8	9	9
12	G	5	8	7
13	H	8	5	7
14	I	8	7	7
15	J	7	5	5
16	K	6	7	8
17
18
19	Table_Fixtures						Table_Controls
20	Col1	Col2	Col3	Col4			Col1	Col2	Col3	Col4
21	A	4	5	6			F	8	9	9
22	B	1	6	3			G	5	8	7
23
24
25	Table_Lamps						Table_Materials
26	Col1	Col2	Col3	Col4			Col1	Col2	Col3	Col4
27	C	6	7	9			H	8	5	7
28	D	9	0	2			I	8	7	7
29
30
31	Table_accessory						Table_Rental
32	Col1	Col2	Col3	Col4			Col1	Col2	Col3	Col4
33	D	7	9	6			J	7	5	5
34	E	5	9	7			K	6	7	8
thewiseguy

Cell Formulas
Range	Formula
A1	A1	=IFERROR(VLOOKUP(A2,VSTACK(Table_Fixtures,Table_Lamps,Table_accessory,Table_Controls,Table_Materials,Table_Rental),4,0),"")
A4	A4	=FORMULATEXT(A5)
A5:D16	A5	=VSTACK(Table_Fixtures,Table_Lamps,Table_accessory,Table_Controls,Table_Materials,Table_Rental)
Dynamic array formulas.