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>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
9SandyMatthewBrown02 9948 8012
10LisaJohnSmith02 9939 4122
11SimonMatthewWeels03 0424 1222
Sheet
 
Upvote 0
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 :)
 
Upvote 0
You're welcome. Thanks for the feedback and welcome to the forum.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

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