Results 1 to 6 of 6

Thread: Vlookup and match?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2006
    Posts
    205
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup and match?

    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

  2. #2
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    548
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Vlookup and match?

    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?

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Posts
    205
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup and match?

    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
    Header1 Header2 Header3 Header4 Header5 Header6
    Item 1 Item 11 Item 1 Item 3 Item 2 Item 7
    Item 5 Item 7 Item 3 Item 5 Item 6 Item 8
    Item 7 Item 3 Item 5 Item 7 Item 10 Item 9
    Item 9 Item 1 Item 7 Item 9 Item 14 Item 10
    Item 7 Item 5 Item 9 Item 11 Item 18 Item 11
    Item 7 Item 13 Item 22 Item 12
    Item 9 Item 15 Item 13
    Item 14
    Item 15
    Table2
    All Items Header1 Header2 Header3 Header4 Header5
    Item 1 Y
    Item 2
    Item 3 Y Y
    Item 4 Y
    Item 5
    Item 6 Y
    Item 7
    Item 8 Y
    Item 9
    Item 10



    Regards

    Dan
    Last edited by Dan1000; Sep 17th, 2019 at 01:49 PM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,246
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vlookup and match?

    How about
    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



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

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Nov 2006
    Posts
    205
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup and match?

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

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,246
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vlookup and match?

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •