Finding Match - Returning separate value

[jwoodarek]

=IF(MATCH(B2,A:A,0),$F:$F)

Hi everyone,

Here's what I'm trying to do:

I want to take a phone number given in B2 and find its match in A:A, printing the F value that corresponds with the match in A. The match formula returns row # "2049", but my IF function returns F2 instead of F2049.

If this makes sense, I would appreciate some thoughts on how to print F(match value)

[etaf]

try
index(F:F,MATCH(B2,A:A,0))

if there is no match - what do you want to happen
use iferror to manage the error

=iferror(index(F:F,MATCH(B2,A:A,0)), "what you want if not found")

[jwoodarek]

Wayne! Thanks for the help!

I've never used the index function, if you don't mind, could you explain how that's working a little? I like to try and learn what I'm doing instead of copying and pasting.

Every number was supposed to have a match, but alas that's not the case.

[RobertMika]

Or
=iferror(vlookup(b2,a:f,6,0), "")

[etaf]

its an alternative to vlookup

the index( f:f
this is looking in the column F:F to return a value

then we use MATCH( B2,A:A,0)

find a match in column AA for B2 , and the 0 means it must be an exact match
now the MATCH tells the index part which row to return the Value in column F from

if no match is found - you get a #N/A
then as thats an error which can be handled by iferror - we can use that function

iferror ( formula , "error return" )
if there is no error - the formula is executed
if there is an error - it will return what ever is after the ,

hope that helps

vlookup - looks up in the left hand column and returns a value from the right hand columns
index/match can return any column

[jwoodarek]

Thanks again! I had the match function down, but was unsure what index was doing. Regardless, thank you for the explanation.

Thank you Rob as well. I tried using vlookup and it wasn't working out the way I wanted. I knew I was close with match, but couldn't find the missing puzzle piece.

[etaf]

the vlookup and the index / match should return the same result

[jwoodarek]

What I meant to say, is I wasn't using vlookup correctly, and I kept messing around with formulas until I finally gave in to ask for help. I tried the vlookup formula Rob gave, and I tried your iferror formula..both ended in errors

[etaf]

in which case you have an issue with the lookup part

you need to test
B2 - column B
and A:A - Column A
find where you think there should be a match - say B2 should = A22
then on a cell try

=B2=A22

and see if that gives TRUE - if not
check there are no leading or trailing spaces (trim() will help )
or that if numbers , one is formatted as a number and the other as text
if a number is text - you will need to use value()
or click on the column and use text to columns to convert
a few other ways exist

lets us know if we can help further - a sample spreadsheet would help