Lookup with Common Value

[Shabbo]

Dear Sir,

My Invoice Number is M1516-1104 and my customer Invoice number is 17/11/2015 MBR/1516/EXD/SER/01827 1104 the common thing is only 1104 in both how do I lookup the same.

 

[Chihiro]

Can you give more than just one line of sample?

Will the last 4 digit be unique?

 

[Shabbo]

Can you give more than just one line of sample?

Will the last 4 digit be unique?

Yes sir,Last 4 digit will be unique.

 

[Chihiro]

Try something like below.
=INDEX($C$6:$C$13,MATCH(TRUE,ISNUMBER(SEARCH(RIGHT(F6,4),$B$6:$B$13)),0))

Confirmed as array (CSE).

EDIT: If you are only interested in last 4 digit of Invoice number and if there is chance same string occurring within another location in the string use below.
=INDEX($C$6:$C$13,MATCH(TRUE,ISNUMBER(SEARCH(RIGHT(F6,4),RIGHT($B$6:$B$13,4))),0))

Confirmed as array (CSE)

 

[Chihiro]

On second thought second formula in above can be simplified to...
=INDEX($C$6:$C$13,MATCH(TRUE,RIGHT(F6,4)=RIGHT($B$6:$B$13,4),0))

Also CSE.

 

[Shabbo]

On second thought second formula in above can be simplified to...
=INDEX($C$6:$C$13,MATCH(TRUE,RIGHT(F6,4)=RIGHT($B$6:$B$13,4),0))

Also CSE.

Dear Sir,
sometimes its needed revert way as well what would be amount for M1516-1104 ?

 

[Khalid NGO]

Hi to all,

One more with just enter:

=SUMPRODUCT((RIGHT($B$6:$B$13,4)=RIGHT(F6,4))*$C$6:$C$13)

Regards,

 

[Shabbo]

Hi to all,

One more with just enter:

=SUMPRODUCT((RIGHT($B$6:$B$13,4)=RIGHT(F6,4))*$C$6:$C$13)

Regards,

Dear Sir,
sometimes its needed revert way as well what would be amount for M1516-1104 ?

 

[Chihiro]

Just reverse the range.
=INDEX($G$6:$G$13,MATCH(TRUE,RIGHT(B6,4)=RIGHT($F$6:$F$13,4),0))

Confirmed as array (CSE).

 

[Khalid NGO]

sometimes its needed revert way as well

Hi,

I suggest to keep/manage your data in a uniform format, then go for calculations.

Regards,