VLookup and IF Formula Needed

T

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
 
Sort by date Sort by votes
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
tandytoo said:
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
Click to expand...

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
Aladin Akyurek said:
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.
Click to expand...
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
tandytoo said:
Yes I did and I double checked all of my columns and fixed the range to the correct # of rows on sheet 2.
Click to expand...

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
You must log in or register to reply here.

Similar threads

A
  • Solved
Pull data from multiple sheets based off name and month selected
Replies
2
Views
248
Amo840812
A
D
  • Question Question
Formula Help
Replies
1
Views
102
Eric W
Eric W
A
  • Question Question
Enter formula with VLOOKUP referencing another file
Replies
4
Views
144
Alan_CT06
A
A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
391
Peter_SSs
Peter_SSs
D
  • Solved
Populating multiple sheet names using Index, Match and Indirect
Replies
7
Views
680
Fluff
Fluff

Share this page

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