Look up phone number by criteria

brandon_clyde

New Member
Joined
Oct 10, 2018
Messages
7
Ok so I have an excel spreadsheet setup.
A B C D
State DEC Name Phone Number


What I am trying to do is search for a number under column B and then return the Phone Number in column D that corresponds on the same row.

=INDEX($B$2:$B$322, MATCH(E2,$D$2:$D$322,0), MATCH($F$2,$D$2:$D$322,0))


This does not work.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
What's in E2 and F2?

VLOOKUP may do here, chnage E2 to the cell containing what you're searching column B for.

=VLOOKUP(E2,B:D,3,0)

INDEX MATCH MATCH is good for when you're not sure which row or column you want, INDEX is where you're searching (so B:D, first match is the row number, second is the column which would be 3 as you know where you're looking.

So you're INDEX MATCH (same as the VLOOKUP) would look something like this.

=INDEX($B$2:$D$322, MATCH(E2,$B$2:$B$322,0), 3)
 
Last edited:
Upvote 0
Column B is 322 5 digit numbers ie(012345) I want to Input a number in E2 an have it searched in column B and then present me with the phone number in the same row found in column D.
 
Upvote 0
Have you tried either of the options provided?
 
Upvote 0
yes and neither are working. I'm wondering if I need to convert the text. It is input like a phone number 333-333-3333
 
Upvote 0
You said you have 5 digit numbers & then gave 012345 as an example, which is 6 digits not 5.
What values do you have in col B & what are you entering in E2?
Also if your numbers begin with a 0 are the numbers stored as text or are they formatted to show a leading 0
 
Upvote 0
Column A Column B Column C Column D
AZ 12345 blah 111-111-1111
AZ 02563 blah 222-222-2222
CA 68942 blah 333-333-3333
CA 45862 blah 444-444-4444
TX 01428 blah 555-555-5555
TX 91254 blah 666-666-6666

here is a sample. now again my goal is to enter a number in E2 that will hopefully match a number in B and Then List the phone Number in D.
 
Upvote 0
You haven't answered my last question.
Are the values in col B text or numbers formatted to show a leading 0, if less than 5 digits?
Also what is the format of E2
 
Upvote 0
In that case the formulae should work. What error do you get?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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