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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,537
Office Version
365
Platform
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,537
Office Version
365
Platform
Windows
You're welcome. Thanks for the feedback and welcome to the forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,182
Messages
5,485,235
Members
407,490
Latest member
leogaleleo84

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top