Help with Match or Find function

[flipit]

Hello great minds,

Im using windows vista with excel 2007.

I have a list of phone numbers in column C and the last four digits to those phone number in column B not in order. Im looking for a function that would give me the address of my partial number in column B that is in column C?

I have use this to find the whole number but now just want to get the same result with only using the last four digits. The below code is not working?

=ADDRESS(MATCH(A2,$B$1:$B$638,0),COLUMN($B$1:$B$638))

 

[carpy1985]

This would create the cell reference as text...

[TABLE="width: 205"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]B19[/TD]
[TD]2000[/TD]
[TD="align: right"]7850932017[/TD]
[/TR]
[TR]
[TD]B18[/TD]
[TD]2001[/TD]
[TD="align: right"]7850932016[/TD]
[/TR]
[TR]
[TD]B17[/TD]
[TD]2002[/TD]
[TD="align: right"]7850932015[/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD]2003[/TD]
[TD="align: right"]7850932014[/TD]
[/TR]
[TR]
[TD]B15[/TD]
[TD]2004[/TD]
[TD="align: right"]7850932013[/TD]
[/TR]
[TR]
[TD]B14[/TD]
[TD]2005[/TD]
[TD="align: right"]7850932012[/TD]
[/TR]
[TR]
[TD]B13[/TD]
[TD]2006[/TD]
[TD="align: right"]7850932011[/TD]
[/TR]
[TR]
[TD]B12[/TD]
[TD]2007[/TD]
[TD="align: right"]7850932010[/TD]
[/TR]
[TR]
[TD]B11[/TD]
[TD]2008[/TD]
[TD="align: right"]7850932009[/TD]
[/TR]
[TR]
[TD]B10[/TD]
[TD]2009[/TD]
[TD="align: right"]7850932008[/TD]
[/TR]
[TR]
[TD]B9[/TD]
[TD]2010[/TD]
[TD="align: right"]7850932007[/TD]
[/TR]
[TR]
[TD]B8[/TD]
[TD]2011[/TD]
[TD="align: right"]7850932006[/TD]
[/TR]
[TR]
[TD]B7[/TD]
[TD]2012[/TD]
[TD="align: right"]7850932005[/TD]
[/TR]
[TR]
[TD]B6[/TD]
[TD]2013[/TD]
[TD="align: right"]7850932004[/TD]
[/TR]
[TR]
[TD]B5[/TD]
[TD]2014[/TD]
[TD="align: right"]7850932003[/TD]
[/TR]
[TR]
[TD]B4[/TD]
[TD]2015[/TD]
[TD="align: right"]7850932002[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD]2016[/TD]
[TD="align: right"]7850932001[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]2017[/TD]
[TD="align: right"]7850932000[/TD]
[/TR]
</tbody>[/TABLE]

="B"&MATCH(RIGHT(C2,4),B:B,0)

is that what you need?

 

[flipit]

i tried it gave a #n/a and a #value could i email you the excel spread sheet?

 

[carpy1985]

so to solve this:

in column A

=IFERROR("B"&MATCH(VALUE(G2),B:B,0),"Not Found")

in column G (a helper column)

=VALUE(RIGHT(C2,4))

 

[Robert Mika]

i tried it gave a #n/a and a #value could i email you the excel spread sheet?

Posted your data here.
Use methods in my signature.