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>
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,746
Office Version
  1. 365
Platform
  1. Windows
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
 

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,746
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback and welcome to the forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,948
Messages
5,621,777
Members
415,856
Latest member
jimb2k

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
Top