Multiple Lookup based on FirstName LastName to return Phone number

nvdunn

New Member
Joined
Nov 4, 2015
Messages
12
HI all
I have been struggling to come up with an excel formula that will look up a person's first name and last name (they are in 2 separate columns) and match both (in case there are > 2 people with the name John) and return the person's phone number.

For each client, I want to be able to look up the relationship manager's firstname and surname and return their business phone number.
i have tried to do this with nested IF statements combining VLOOKUPS but it's just not working.
I cannot combine FIrstName Last Name as the data setup i will be looking up will be large and is from a database dump extract so want to use the columns it outputs.
Any help is very much appreciated.
Many thanks




CompanyFirst NameLast NamePosition DescrBusiness PhoneBusiness FaxBusiness Mobile
Wealth ManagementMatthewBrownManager02 9948 8012
Wealth ManagementJohnSmithSales02 9939 4122
Wealth ManagementMatthewWeelsMarketing03 0424 1222
Client NameRelationship Manager FirstNameRelationship Manager LastNameRelationship Mger Phone
SandyMatthewBrown=VLOOKUP(B13 &" " &C13, B2:E4, 4, FALSE)
LisaJohnSmith
SimonMatthewWeels

<tbody>
</tbody>
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,308
Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER (command+return on a MAC).
The formula in D9 can just be copied down. Change ranges to match your data.
Excel Workbook
ABCDEFG
1CompanyFirst NameLast NamePosition DescrBusiness PhoneBusiness FaxBusiness Mobile
2Wealth ManagementMatthewBrownManager02 9948 8012
3Wealth ManagementJohnSmithSales02 9939 4122
4Wealth ManagementMatthewWeelsMarketing03 0424 1222
5
6
7
8Client NameRelationship Manager FirstNameRelationship Manager LastNameRelationship Mger Phone
9SandyMatthewBrown
10LisaJohnSmith
11SimonMatthewWeels
Sheet
 

nvdunn

New Member
Joined
Nov 4, 2015
Messages
12
Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER (command+return on a MAC).
The formula in D9 can just be copied down. Change ranges to match your data.

*ABCDEFG
1CompanyFirst NameLast NamePosition DescrBusiness PhoneBusiness FaxBusiness Mobile
2Wealth ManagementMatthewBrownManager02 9948 8012**
3Wealth ManagementJohnSmithSales02 9939 4122**
4Wealth ManagementMatthewWeelsMarketing03 0424 1222**
5*******
6*******
7*******
8Client NameRelationship Manager FirstNameRelationship Manager LastNameRelationship Mger Phone***
9SandyMatthewBrown02 9948 8012***
10LisaJohnSmith02 9939 4122***
11SimonMatthewWeels03 0424 1222***

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:95px;"><col style="width:104px;"><col style="width:98px;"><col style="width:115px;"><col style="width:107px;"><col style="width:78px;"><col style="width:85px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
D9{=INDEX($E$2:$E$4,MATCH($B9&$C9,$B$2:$B$4&$C$2:$C$4,0))}
D10{=INDEX($E$2:$E$4,MATCH($B10&$C10,$B$2:$B$4&$C$2:$C$4,0))}
D11{=INDEX($E$2:$E$4,MATCH($B11&$C11,$B$2:$B$4&$C$2:$C$4,0))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Thank you so much for your help. That worked well. You're a genius! I struggled for a long time before I posted this so thank you very much for your time :)
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,308
You're welcome. Thanks for the feedback and welcome to the forum.
 

Forum statistics

Threads
1,077,907
Messages
5,337,096
Members
399,125
Latest member
manibiotech

Some videos you may like

This Week's Hot Topics

Top