Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Struggling with VLOOKUP with multiple col_index_num to return text

This is a discussion on Struggling with VLOOKUP with multiple col_index_num to return text within the Excel Questions forums, part of the Question Forums category; Hi, I'm really hoping somebody could please help me with this formula I've been struggling with all day and is ...

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    14

    Question Struggling with VLOOKUP with multiple col_index_num to return text

    Hi, I'm really hoping somebody could please help me with this formula I've been struggling with all day and is making me rather upset.
    This is the scenario: on Sheet1 I have a concatenated column (column A)with Fname + Lname and I want to match that same name to another worksheet (called 'lookup') and return the value that's in the header of that worksheet.

    This is 'Sheet1' (I hid some columns)
    Concatenated Name
    column A


    First name
    column C


    Last name
    column D


    Organizational Unit
    column G


    Old Region
    H


    Aftab C***AftabC***Region 6 - Field
    Albert H***AlbertH***Region 8 - Field
    Alexander S***AlexanderS***Region 1 - Field
    Allan B***AllanB***Region 5 - Field
    Andrea D***AndreaD***Region 4 - Field
    Apolinar V***ApolinarV***Region 8 - Field


    Worksheet 'lookup'
    US_FS_CEUS_FS_CWUS_FS_MCUS_FS_NEUS_FS_SE
    Yichong L***Christopher S***Aftab C***Billy M***Robert D***
    David A***Michael L***John L***Hector F***
    Charles T***Jerel J***Christien D***Josue R***
    Houston L***Christopher S***Christien D***Hector F***


    Example: =VLOOKUP(Sheet1!A2, lookup!A2:F500,) starting in H2

    So, I want to search for 'Aftab C***' which is in Sheet1 A2 anywhere in sheet 'lookup' in the array A2: F800 and return the column header that it's in, which is US_FS_MC in cell H2 on Sheet1...

    I would be very grateful if somebody could write the formula for me! I just can't figure it out and it's literally making ill.... I need to do this for work

  2. #2
    Board Regular shemayisroel's Avatar
    Join Date
    Sep 2008
    Location
    Sydney - Australia
    Posts
    1,859

    Default Re: Struggling with VLOOKUP with multiple col_index_num to return text

    Quote Originally Posted by Darg View Post
    So, I want to search for 'Aftab C***' which is in Sheet1 A2 anywhere in sheet 'lookup' in the array A2: F800 and return the column header that it's in, which is US_FS_MC in cell H2 on Sheet1...
    Hi & Welcome,

    Just so I'm sure on what you are wanting to achive, You would want to search for a name in Sheet 1, that exists also in Sheet 2, and if a match is found what do you want ot return?

    As in the above there is no header in Sheet 1 called US_FS_MC...

    Please advise.
    enercheenhologoskaihoogosenprostontheonkaitheosenhologosnarchhnoogovkailogohnprvtonqeonkaiqeovhnologov

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,338

    Default Re: Struggling with VLOOKUP with multiple col_index_num to return text

    Try...

    H2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

    =INDEX(lookup!$A$1:$F$1,SMALL(IF(lookup!$A$2:$F$500=Sheet1!A2,COLUMN(lookup!$A$2:$F$500)-COLUMN(lookup!$A$2)+1),1))

    Hope this helps!

  4. #4
    New Member
    Join Date
    Jan 2009
    Posts
    14

    Default Re: Struggling with VLOOKUP with multiple col_index_num to return text

    THANK YOU, DOMENIC!! THANK YOU!! It works wonderfully... I could've never come up with this myself, you made my day!

  5. #5
    New Member
    Join Date
    Jan 2009
    Posts
    14

    Default Re: Struggling with VLOOKUP with multiple col_index_num to return text

    One more quick question Domenic... Is it possible to add a wildcard to the formla when it's referencing the name. I noticed a few people have nicknames in parenthesis so the formula wasn't able to catch those....

    If you could kindly show me how to do this that would be awesome!

    Michelle

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,338

    Default Re: Struggling with VLOOKUP with multiple col_index_num to return text

    I'm assuming that the nickname in parenthesis occurs after the name, and that the nicknames occur in worksheet 'lookup'. If this is correct, try...

    =INDEX(lookup!$A$1:$F$1,SMALL(IF(LEFT(lookup!$A$2:$F$500,LEN(Sheet1!A2))=Sheet1!A2,COLUMN(lookup!$A$2:$F$500)-COLUMN(lookup!$A$2)+1),1))

    ...confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

  7. #7
    New Member
    Join Date
    Jan 2009
    Posts
    14

    Default Re: Struggling with VLOOKUP with multiple col_index_num to return text

    Actually, this occurs on the 'lookup' sheet as 'Charles (Cappy) Plo**'....

    Thanks!

  8. #8
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,338

    Default Re: Struggling with VLOOKUP with multiple col_index_num to return text

    Try...

    =INDEX(lookup!$A$1:$F$1,SMALL(IF(ISNUMBER(SEARCH(Sheet1!C2&"*"&Sheet1!D2,lookup!$A$2:$F$500)),COLUMN(lookup!$A$2:$F$500)-COLUMN(lookup!$A$2)+1),1))

    ...confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

  9. #9
    New Member
    Join Date
    Jan 2009
    Posts
    14

    Default Re: Struggling with VLOOKUP with multiple col_index_num to return text

    Hhmm... it didn't work. The value is skewed..

  10. #10
    New Member
    Join Date
    Jan 2009
    Posts
    14

    Default Re: Struggling with VLOOKUP with multiple col_index_num to return text

    Maybe perhaps if I created a formula that deletes anything in parenthesis, then they would match. Just an idea... Thanks!!

Page 1 of 2 12 LastLast

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
  •  


DMCA.com