Vlookup and match?

[Dan1000]

Two tables of data...

I have table1 with headers which I need to do a horizontal lookup on to match table2 with the same headers

I have a serious of columns of data in table2 which I then need to do a vlookup from - find a specific cell in a specific column,

Is it possible to do this using a vlookup and a match together - using the match on the headers in table1 first then doing the vlookup from table1 to table2, all as one formula?


Any help would be much appreciated


Dan

 

[RasGhul]

Hi Dan,

Index/Match/Match is much faster, have you converted your data to actual tables?

Can you post some sample data so we can help?

 

[Dan1000]

[TABLE="width: 349"]
<tbody>[TR]
[TD="colspan: 6"]Hi RasGhul

I know the Ys do not necessarily match in the following sample - its just to show the idea...

...also, the formula is in all cells but shows blank where the item does not exist...

...the 'Y's are where the formula returns 'Y' where the header and then the left most column matches (needs to be in that order does it not?), bearing in mind this is very simplified. The table2 I have has several thousand items in the left column and also I would like the headers to be in any order not alphabetical or numerical and so I believe Index Match would be the way to go especially with respect to it being faster as you say and I know is the case.

Table1[/TD]
[/TR]
[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header4[/TD]
[TD]Header5[/TD]
[TD]Header6[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Item 11[/TD]
[TD]Item 1[/TD]
[TD]Item 3[/TD]
[TD]Item 2[/TD]
[TD]Item 7[/TD]
[/TR]
[TR]
[TD]Item 5[/TD]
[TD]Item 7[/TD]
[TD]Item 3[/TD]
[TD]Item 5[/TD]
[TD]Item 6[/TD]
[TD]Item 8[/TD]
[/TR]
[TR]
[TD]Item 7[/TD]
[TD]Item 3[/TD]
[TD]Item 5[/TD]
[TD]Item 7[/TD]
[TD]Item 10[/TD]
[TD]Item 9[/TD]
[/TR]
[TR]
[TD]Item 9[/TD]
[TD]Item 1[/TD]
[TD]Item 7[/TD]
[TD]Item 9[/TD]
[TD]Item 14[/TD]
[TD]Item 10[/TD]
[/TR]
[TR]
[TD]Item 7[/TD]
[TD]Item 5[/TD]
[TD]Item 9[/TD]
[TD]Item 11[/TD]
[TD]Item 18[/TD]
[TD]Item 11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item 7[/TD]
[TD][/TD]
[TD]Item 13[/TD]
[TD]Item 22[/TD]
[TD]Item 12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item 9[/TD]
[TD][/TD]
[TD]Item 15[/TD]
[TD][/TD]
[TD]Item 13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Table2[/TD]
[/TR]
[TR]
[TD]All Items[/TD]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header4[/TD]
[TD]Header5[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Item 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 8[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Regards

Dan

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Table1							Table 2
2	Header1	Header2	Header3	Header4	Header5	Header6		All Items	Header1	Header2	Header3	Header4	Header5
3	Item 1	Item 11	Item 1	Item 3	Item 2	Item 7		Item 1	Y	Y	Y
4	Item 5	Item 7	Item 3	Item 5	Item 6	Item 8		Item 2					Y
5	Item 7	Item 3	Item 5	Item 7	Item 10	Item 9		Item 3		Y	Y	Y
6	Item 9	Item 1	Item 7	Item 9	Item 14	Item 10		Item 4
7	Item 7	Item 5	Item 9	Item 11	Item 18	Item 11		Item 5	Y	Y	Y	Y
8		Item 7		Item 13	Item 22	Item 12		Item 6					Y
9		Item 9		Item 15		Item 13		Item 7	Y	Y	Y	Y
10						Item 14		Item 8
11						Item 15		Item 9	Y	Y	Y	Y
12								Item 10					Y
RC

Cell Formulas
Range	Formula
I3	=IF(ISERROR(MATCH($H3,INDEX($A$3:$F$11,0,MATCH(I$2,$A$2:$F$2,0)),0)),"","Y")

 

[Dan1000]

Nice one Fluff - just what I was looking for - thank you!!!

 

[Fluff]

Glad to help & thanks for the feedback