Vlookup and match?

Dan1000

Board Regular
Joined
Nov 4, 2006
Messages
210
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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
Header1Header2Header3Header4Header5Header6
Item 1Item 11Item 1Item 3Item 2Item 7
Item 5Item 7Item 3Item 5Item 6Item 8
Item 7Item 3Item 5Item 7Item 10Item 9
Item 9Item 1Item 7Item 9Item 14Item 10
Item 7Item 5Item 9Item 11Item 18Item 11
Item 7Item 13Item 22Item 12
Item 9Item 15Item 13
Item 14
Item 15
Table2
All ItemsHeader1Header2Header3Header4Header5
Item 1Y
Item 2
Item 3YY
Item 4Y
Item 5
Item 6Y
Item 7
Item 8Y
Item 9
Item 10

<tbody>
</tbody>



Regards

Dan
 
Last edited:
Upvote 0
How about

Book1
ABCDEFGHIJKLM
1Table1Table 2
2Header1Header2Header3Header4Header5Header6All ItemsHeader1Header2Header3Header4Header5
3Item 1Item 11Item 1Item 3Item 2Item 7Item 1YYY
4Item 5Item 7Item 3Item 5Item 6Item 8Item 2Y
5Item 7Item 3Item 5Item 7Item 10Item 9Item 3YYY
6Item 9Item 1Item 7Item 9Item 14Item 10Item 4
7Item 7Item 5Item 9Item 11Item 18Item 11Item 5YYYY
8Item 7Item 13Item 22Item 12Item 6Y
9Item 9Item 15Item 13Item 7YYYY
10Item 14Item 8
11Item 15Item 9YYYY
12Item 10Y
RC
Cell Formulas
RangeFormula
I3=IF(ISERROR(MATCH($H3,INDEX($A$3:$F$11,0,MATCH(I$2,$A$2:$F$2,0)),0)),"","Y")
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top