# 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. ## 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*** Aftab C*** Region 6 - Field Albert H*** Albert H*** Region 8 - Field Alexander S*** Alexander S*** Region 1 - Field Allan B*** Allan B*** Region 5 - Field Andrea D*** Andrea D*** Region 4 - Field Apolinar V*** Apolinar V*** Region 8 - Field

Worksheet 'lookup'
 US_FS_CE US_FS_CW US_FS_MC US_FS_NE US_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. ## Re: Struggling with VLOOKUP with multiple col_index_num to return text

Originally Posted by Darg
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...

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Struggling with VLOOKUP with multiple col_index_num to return text

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

10. ## 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 Last

#### Posting Permissions

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