• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

vlookup assistance

I have a workbook with two tabs (worksheets) team & regs

tab teams has multiple columns and I want to have a column pull email from tab regs when the fname and lastname columns match in each table.

tab teams
A=fname
B=lname
H=pull from email

tab regs
A=lname
B=fname
G=email

so in teams I want column h to be a lookup that pulls column G from regs when the fname & lname matches
0
Matt Pinkston
Asked:
Matt Pinkston
  • 3
1 Solution
 
bharrington83Commented:
According to http://www.mrexcel.com/forum/showthread.php?t=306615, you can use the index function for a multiple criteria function. Structure is thus:

=INDEX(D1:D100,MATCH(1,(A1:A100="x")*(B1:B100="y")*(C1:C100="z"),0))

Open in new window

0
 
bharrington83Commented:
Also, you could use vbscript to help with it, here's a link to a guide on it:

http://excelspreadsheetshelp.blogspot.com/search/label/LOOKUP
0
 
Matt PinkstonAuthor Commented:
not vlookup?
0
 
bharrington83Commented:
Vlookup only considers one conditional.  You look up by the left most column of your array.  If you'd like to use vlookup and are okay with adding a column, you could do this.

Add Column A to both sheets, set to =Concantenate(B2," ",C2) in both A Columns

Then do your vlookup off that.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

Index/match is much more powerful than Vlookup. Here's another version to do what you describe, starting in G2 and copied down.

=INDEX(regs!$C$1:$C$20,MATCH(teams!A2&teams!B2,INDEX(regs!$A$1:$A$20&regs!$B$1:$B$20,0),0))

see attached.

cheers, teylyn
27633482.xlsx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now