Search code examples
excelreturnfindmatch

How to match partial values in different columns excel

Hope you are doing well.

I am new in this team and in this tech world and I would appreciate some of your time and experience.

I want to check if a partial number in a cell exists in another cell of another column, and return the whole value.

Specifically, in the image.

Some cells in Column F contain a partial number of column C.

is there any formula to help me with that?

enter image description here

I tried to use FIND and VLOOKUP, but since I am inexperienced I had not a success

Here is the sample data:

Number Partial Number (last 5 digits)
900226001157835 90305
900108001389872 57479
900108001389588 17288
900108001389529 57577
900108001389856 57850
900108001389602 89840
900108001389792 90217
900108001389680 89137
900226001756925 90307
900108001480303 89911
900226001157404
900226001157479
900108001617288
900108001390456
900108001389228
900108001481627
900108001389282
900108001390113
900108001390012
900108001389267
900108001389092
900108001389840
900108001389937
900108001389479
900108001617212
900108001617292
900226001157850
Solution

  • Assuming no Excel version constraints as per the tags listed in the question. You can put the following formula in cell D1 to look for a number match, considering the last 5 digits:

    =LET(A, A1:A27, B, B1:B10,BYROW(B, LAMBDA(bb, TEXTJOIN(",",,FILTER(A,
 ISNUMBER(XMATCH(1*RIGHT(A,5), bb)), "Not Found")))))

    If you don't expect to find more than one result per partial number, then you can remove the TEXTJOIN call.

    Here is the output: excel output

    The input data is stored as a number in Excel, that is why after RIGHT call I need to convert it back to a number, multiplying it by 1. If the input data are numbers stored as text, it is not really necessary.