Match using Left Right function

[cshetty]

Dears

In column A I have list of unique codes and I want to get the code based on input of last 4 characters of the code. Please help me to get a formula.

e.g., There is a code in cell A5, which is 80007458 and in input cell when I enter 7458, I should get the full code in result cell.

Theoretically the equation should be
INDEX (A1:A100, MATCH (input value, RIGHT (A1:A100,4),0))

Thank you

 

[AhoyNC]

Try changing your formula to:
INDEX (A1:A100, MATCH (input value, RIGHT (A1:A100,4)*1,0))

The RIGHT function is going to return text. My guess is you are entering 7458 as a number, so you aren't getting a match. By multiplying the RIGHT function by 1 it will change to a numeric value.

 

[cshetty]

Try changing your formula to:
INDEX (A1:A100, MATCH (input value, RIGHT (A1:A100,4)*1,0))

The RIGHT function is going to return text. My guess is you are entering 7458 as a number, so you aren't getting a match. By multiplying the RIGHT function by 1 it will change to a numeric value.

So this should be an array formula, right ? Works now. Thanks

 

[Peter_SSs]

You could also use VLOOKUP, or MOD which avoids any text/numerical conversions. I assume both will still require array entry in your version. Perhaps the AGGREGATE version does not require array entry though?

BTW, if marking a post as the solution, please mark the post that actually provided the solution. So here I have change the mark from post 3 to post 2.

22 11 08.xlsm
	A	B	C	D	E	F
1	Code
2	80007455		7458	80007458	80007458	80007458
3	80007456
4	80007457
5	80007458
6	80007459
7	80007460
8	80007461
get code

Cell Formulas
Range	Formula
D2	D2	=VLOOKUP("*"&C2,A1:A100&"",1,0)+0
E2	E2	=INDEX(A2:A10,MATCH(C2,MOD(A2:A10,10000),0))
F2	F2	=AGGREGATE(15,6,A2:A10/(MOD(A2:A10,10000)=C2),1)

 

[cshetty]

Peter Sir,

trying to understand this formula, how it worked
=AGGREGATE(15,6,A2:A10/(MOD(A2:A10,10000)=C2),1)
15 = Small
6 = Ignore error
A2:A10/(MOD(A2:A10,10000)=C2) = This part i am not getting it. Please help me to understand better,

 

[cshetty]

Peter Sir,

trying to understand this formula, how it worked
=AGGREGATE(15,6,A2:A10/(MOD(A2:A10,10000)=C2),1)
15 = Small
6 = Ignore error
A2:A10/(MOD(A2:A10,10000)=C2) = This part i am not getting it. Please help me to understand better,

If I am not wrong , (MOD(A2:A10,10000)=C2) is to ascertain True or false so that when we devide the code by the result of this formula, either returns error or the code we are looking for. All other results in an error , so the smallest one is the one we are looking for.

Thank you Sir.

 

[Peter_SSs]

(MOD(A2:A10,10000)=C2) is to ascertain True or false so that when we devide the code by the result of this formula, either returns error or the code we are looking for.

Looks like you have figured it out.