# vlookup for last Five digits

#### naresh55

##### New Member
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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Robertvk

##### Board Regular
in C1

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

Something like that?

#### naresh55

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

#### Robertvk

##### Board Regular
Code:
``=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...

Code:
``=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.

Last edited:

#### vicedo

##### Active Member

This should work

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

#### naresh55

##### New Member
Thanks lotit works could see expected results

##### MrExcel MVP
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)

1,127,155
Messages
5,623,074
Members
415,950
Latest member
Kindz

### 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.

### Which adblocker are you using?

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

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