VLookup and IF Formula Needed

tandytoo

New Member
Joined
Jul 22, 2009
Messages
5
Hello,

I am using office 2003 and have the following data on two tabs (there are about 4000 employees):

Sheet 1
Column A - first name - ex: Joe
Column B - last name - ex: Smith
Column C - emp # - want to pull emp # from sheet #2 based on match with first and last name

Sheet 2
Column A - first name - ex: Joe
Column B - last name - ex: Smith
Column C - emp # - ex: 11111

The problem is that I have several last names that are the same so a regular Vlookup pulls the same employee # for all Smiths. I don't know how to include a match based on the first name.

Any help would be appreciated.
Thanks
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You can use an array formula like the following (must be entered using CTRL+SHIFT+ENTER, not just ENTER):

=INDEX(Sheet2!C:C,MATCH(A1&B1,Sheet2!A:A&Sheet2!B:B,0))

If you create a helper column on both sheets that includes the concatenated first and last names, you could then use a non-array VLOOKUP or INDEX/MATCH.
 
Upvote 0
Hello,

I am using office 2003 and have the following data on two tabs (there are about 4000 employees):

Sheet 1
Column A - first name - ex: Joe
Column B - last name - ex: Smith
Column C - emp # - want to pull emp # from sheet #2 based on match with first and last name

Sheet 2
Column A - first name - ex: Joe
Column B - last name - ex: Smith
Column C - emp # - ex: 11111

The problem is that I have several last names that are the same so a regular Vlookup pulls the same employee # for all Smiths. I don't know how to include a match based on the first name.

Any help would be appreciated.
Thanks

Sheet1

C2:

Control+shift+enter, not just enter...
Code:
=INDEX(Sheet2!$C$2:$C$400,
   MATCH(1,IF(Sheet2!$A$2:$A$400=A2,
    IF(Sheet2!$B$2:$B$400=B2,1)),0))

Adjust the ranges to suit.

Another option is:

Sheet2

D2, copy down:

=A2&"|"&B2

Then invoke on Sheet1, in C2:

=INDEX(Sheet2!$C$2:$C$400,MATCH(A2&"|"&B2,Sheet2!$D$2:$D$400,0))


and copy down.
 
Upvote 0
Hi

Easiest thing to do is use a helper column to join the first and last name.

=B2&C2&COUNTIF($C$2:C2, C2)

The bit in red should add a unique identifier should you have a few folk with popular names John Smith for example. You can then use this unique value to lookup the tables
 
Upvote 0
In sheet1, cell C1:

=INDEX(Sheet2!$C$1:$C$100,MATCH(1,INDEX((Sheet2!$A$1:$A$100=Sheet1!A1)*(Sheet2!$B$1:$B$100=Sheet1!B1),0)),0)

Copy down.
 
Upvote 0
Sheet1

C2:

Control+shift+enter, not just enter...
Code:
=INDEX(Sheet2!$C$2:$C$400,
   MATCH(1,IF(Sheet2!$A$2:$A$400=A2,
    IF(Sheet2!$B$2:$B$400=B2,1)),0))

Adjust the ranges to suit.

Another option is:

Sheet2

D2, copy down:

=A2&"|"&B2

Then invoke on Sheet1, in C2:

=INDEX(Sheet2!$C$2:$C$400,MATCH(A2&"|"&B2,Sheet2!$D$2:$D$400,0))


and copy down.
Thank you for your formula. I tried it and got #NA in all of the cells. Did I do something wrong when I copied it?

Teresa
 
Upvote 0
Yes I did and I double checked all of my columns and fixed the range to the correct # of rows on sheet 2.
 
Upvote 0
Yes I did and I double checked all of my columns and fixed the range to the correct # of rows on sheet 2.

Teresa,

What follows is a recap...

Sheet1, A2:B4...

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=left>Joe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=left>Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>11111</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=left>Dan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=left>Doe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>22222</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=left>Chris</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=left>Jona</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=middle x:err="#N/A">#N/A</TD></TR></TBODY></TABLE>

C2:

Control+shift+enter, not just enter...
Code:
=INDEX(Sheet2!$C$2:$C$400,
   MATCH(1,IF(Sheet2!$A$2:$A$400=A2,
    IF(Sheet2!$B$2:$B$400=B2,1)),0))

Sheet2, A2:C4...

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=left>Joe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=left>Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>11111</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=left>Dan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=left>Doe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>22222</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=left>Fad</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=left>Draga</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>21345</TD></TR></TBODY></TABLE>

If this setup fails, you might have entries on both Sheet1 and Sheet2
with stray spaces around them. You can clean up your data with
the free ASAP Utilities...
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
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