vlookup for last Five digits

[naresh55]

Hi Guys,

I have data in Column A and Column B, I need to check the last Five digits of Column A in Column B last Five digits.

Can anyone help to get the formula.

Column A	Column B
80059782	CFQ0060068
80059874	CFQ0059919
80059875	CFQ0059918
80059891	CFQ0059915
80059892	CFQ0059892
80059915	CFQ0059891
80059918	CFQ0059875
80059919	CFQ0059874
80060068	CFQ0059782

<colgroup><col><col></colgroup><tbody>
</tbody>

Ex: Column A cell A1 "59782" to check in Column B if found return yes

 

[Robertvk]

in C1

=IFERROR(Match("*"&right(A1,5)B1:B50;0)>1;"no")

Something like that?

 

[naresh55]

Thanks one more information how to return value cell "A2" if found in Column B instead "True"

 

[Robertvk]

=IF(IFERROR(MATCH("*"&RIGHT(B1;5);A1:A50;0)>=1;"no");INDIRECT("A"&MATCH("*"&RIGHT(B1;5);A1:A50;0));"no")

This works for me. Replace all ";" with "," if you have a different language.

Oh wait you asked something different...

=IF(IFERROR(MATCH("*"&RIGHT(B1;5);A1:A50;0)>=1;"no");B1;"no")

I guess this is what you meant. This will return your search value if your search was succesfull.

 

[vicedo]

This should work

=VLOOKUP("*"&RIGHT(A2,5),$B$2:$B$10,1,FALSE)

 

[naresh55]

Thanks lotit works could see expected results

 

[Aladin Akyurek]

Not sure what you opted for. Here is a different take:

Row\Col	A​	B​	C​
1​
2​	80059782	CFQ0060068	CFQ0059782
3​	80059874	CFQ0059919	CFQ0059874
4​	80059875	CFQ0059918	CFQ0059875
5​	80059891	CFQ0059915	CFQ0059891
6​	80059892	CFQ0059892	CFQ0059892
7​	80059915	CFQ0059891	CFQ0059915
8​	80059918	CFQ0059875	CFQ0059918
9​	80059919	CFQ0059874	CFQ0059919
10​	90597820	CFQ0597820	CFQ0597820
11​	80060068	CFQ0059782	CFQ0060068
12​

In C2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(RIGHT(A2,5)&"|",$B$2:$B$11&"|"),$B$2:$B$11)