Look up phone number by criteria

[brandon_clyde]

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.

 

[mrshl9898]

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)

 

[brandon_clyde]

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.

 

[Fluff]

Have you tried either of the options provided?

 

[brandon_clyde]

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

 

[Fluff]

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

 

[brandon_clyde]

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.

 

[Fluff]

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

 

[brandon_clyde]

Column B is formatted as special character for Zip Code same as E2.
Column D o formatted as Text.

 

[Fluff]

In that case the formulae should work. What error do you get?