Multi Column vLookup
Results 1 to 9 of 9

Thread: Multi Column vLookup
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multi Column vLookup

    I have a 5 column table - Column 1 thru Column 4 contain unique names; Column 5 contains a time value. I am attempting to use vLookup to search for a unique name that may appear in Column 1 thru Column 4 and return the time value in the adjacent Column 5. I have had no success in developing this formula and would appreciate some help.

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Multi Column vLookup

    Try:

    D E F G H I J K L M
    1 Name1 Name2 Name3 Name4 Date Name Date
    2 a b c d 1-Jan k 1-Feb 1-Feb
    3 e f g h 2-Jan
    4 i j k l 1-Feb
    5 m n o p 1-Mar
    Sheet9

    Array Formulas
    Cell Formula
    L2 {=INDEX(Table1[Date],MATCH(1,(MMULT(--(Table1[[Name1]:[Name4]]=K2),ROW(Table1[Name1])^0)>0)+0,0))}
    M2 {=INDEX(H2:H5,MATCH(1,(MMULT(--(D2:G5=K2),ROW(H2:H5)^0)>0)+0,0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    The L2 formula is if you have an actual defined table, the M2 formula if you just use range addresses.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Multi Column vLookup

    Heres an option:

    =INDEX($E$2:$E$20,AGGREGATE(15,6,1/($A$2:$D$20="LookupValue")*(ROW($A$2:$D$20)-ROW($A$2)+1),1))

  4. #4
    New Member
    Join Date
    Apr 2017
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi Column vLookup

    Eric...this is great, thanks...is there a way to create the above solution without using an array formula?

  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Multi Column vLookup

    steve the fish provided a non-array formula to do that. (Technically speaking, it still is an array formula under the covers, it just doesn't require the CSE when entering.)

    If you want to use the table syntax, then this is how to convert that format:

    =INDEX(Table1[Date],AGGREGATE(15,6,1/(Table1[[Name1]:[Name4]]=K2)*(ROW(Table1[[Name1]:[Name4]])-ROW(INDEX(Table1[Name1],1))+1),1))
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  6. #6
    New Member
    Join Date
    Apr 2017
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi Column vLookup

    Steve...perfect solution without using an array...thanks so much

  7. #7
    New Member
    Join Date
    Apr 2017
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi Column vLookup

    Thanks so much...I just incorporated the formula into my workbook and it works GREAT!

  8. #8
    New Member
    Join Date
    May 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi Column vLookup

    Hi,

    The DGET formula is a great alternative to the vLookup function. The main difference is that it allows the user to use multiple criteria.

    Here is a short video that explains it in more detail. Hope this helps!

    https://www.youtube.com/watch?v=ZIsxd__5Wyk

  9. #9
    New Member
    Join Date
    Apr 2017
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi Column vLookup

    Alex89...thanks, I like this function (never used it before), unfortunately, I was unable to adopt it to solve my issue. I need to find a single value that could appear in any of the 1st 4 columns and then use the adjacent value in Column 5. Eric W and Steve the fish supplied me with a solution.

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
  •