Match based on part of cell

[mma3824]

Hello,

I have a project where I am trying to link cells to a separate table and need to match the data based on the last part of a number sequence.

Below is an example.

In the first group of information I am trying to find 001HO07235 or 501HO07235 in the data table which is the second group of information. In the second group of information (the large data table) these numbers are only listed starting with 001. This large data table will not have any numbers starting with 501. The issue is 001 and 501 numbers reference the same item (HO07235) just in a different presentation. I simply want to find the HO07235 and give the price associated with it.

Thank you in Advance!


Cells I am referencing What result should be
001HO07235 10
501HO07235 10



Large data table that houses data wanting to link to above

Number Price
001HO07235 10

[jomaor1]

HI,

I didn't quite understand, but here it goes:

Have you tried with concatenated Vlookup? like this:

=IFERROR(VLOOKUP("001"&A1,Sheet2!A:B,2,0),VLOOKUP("501"&A1,Sheet2!A:B,2,0))

Can you elaborate more, or attach a workbook? Thanks!

[mma3824]

Hello,

Here is a excel file as an example.

I want to match the ID's without a price to the ID's with a price based on the last 7 digits of the ID's.

Thanks!

[Phuocam]

Maybe?

=VLOOKUP("*"&RIGHT(A5,7),$G$5:$I$6,3,0)