Struggling with VLOOKUP with multiple col_index_num to return text

Darg

New Member
Joined
Jan 5, 2009
Messages
16
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)
<TABLE style="WIDTH: 497pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=663 border=0 x:str><COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6692" width=183><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6692" width=183><TBODY><TR style="HEIGHT: 27pt; mso-height-source: userset" height=36><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 104pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 27pt; BACKGROUND-COLOR: #ffff99" width=138 height=36>Concatenated Name
column A


</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff99" width=75>First name
column C


</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 63pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff99" width=84>Last name
column D


</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 137pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff99" width=183>Organizational Unit
column G


</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 137pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff99" width=183>Old Region
H


</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Aftab C***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Aftab</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">C***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Region 6 - Field</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Albert H***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Albert</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">H***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Region 8 - Field</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Alexander S***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Alexander</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">S***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Region 1 - Field</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Allan B***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Allan</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">B***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Region 5 - Field</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Andrea D***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Andrea</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">D***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Region 4 - Field</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Apolinar V***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Apolinar</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">V***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Region 8 - Field</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

Worksheet 'lookup'
<TABLE style="WIDTH: 588pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=782 border=0 x:str><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7241" width=198><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7241" width=198><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl416 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 94pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=125 height=17>US_FS_CE</TD><TD class=xl417 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 149pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" width=198>US_FS_CW</TD><TD class=xl417 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 91pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" width=121>US_FS_MC</TD><TD class=xl416 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 105pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" width=140>US_FS_NE</TD><TD class=xl416 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 149pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" width=198>US_FS_SE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Yichong L***</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Christopher S***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Aftab C***</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Billy M***</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Robert D***</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>David A***</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Michael L***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">John L***</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Hector F***</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Charles T***</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Jerel J***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Christien D***</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Josue R***</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Houston L***</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Christopher S***</TD><TD class=xl414 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Christien D***</TD><TD class=xl415 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Hector F***</TD></TR></TBODY></TABLE>

Example: =VLOOKUP(Sheet1!A2, lookup!A2:F500,<RETURN US_FS_CE ie. matches, value where header>) 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 :confused:
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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!
 
Upvote 0
THANK YOU, DOMENIC!! THANK YOU!! It works wonderfully... I could've never come up with this myself, you made my day! :)
 
Upvote 0
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 :)
 
Upvote 0
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!
 
Upvote 0
Actually, this occurs on the 'lookup' sheet as 'Charles (Cappy) Plo**'....

Thanks!
 
Upvote 0
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!
 
Upvote 0
Maybe perhaps if I created a formula that deletes anything in parenthesis, then they would match. Just an idea... Thanks!! :)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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