vlookup for last Five digits

naresh55

New Member
Joined
Apr 21, 2014
Messages
24
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 AColumn B
80059782CFQ0060068
80059874CFQ0059919
80059875CFQ0059918
80059891CFQ0059915
80059892CFQ0059892
80059915CFQ0059891
80059918CFQ0059875
80059919CFQ0059874
80060068CFQ0059782

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


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

Some videos you may like

Excel Facts

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

Robertvk

Board Regular
Joined
Oct 15, 2015
Messages
121
in C1

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

Something like that?
 

naresh55

New Member
Joined
Apr 21, 2014
Messages
24
Thanks:) one more information how to return value cell "A2" if found in Column B instead "True"
 

Robertvk

Board Regular
Joined
Oct 15, 2015
Messages
121
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
Joined
Jan 9, 2015
Messages
401

ADVERTISEMENT

This should work

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top