Currently, I have a workbook with two sheets. In one sheet, there's a column that contains a series of numbers with an underscore, various periods, and a varying amount of text. In another sheet, I have a column that contains the same numbers, but no text.

For example:

Column A

31383_1.1.mgs
32164_1.12 2.11 SR-0174

Column B

31383_1.1
32164_1.12

What I'm looking to do is build a formula that marks Column B as green (or whatever, just some formatting to indicate Yes) that, for example, both Column A and Column B contain 31383_1.1 despite the fact that Column A also contains the .msg text. I've ran through various Match, IF, XLOOKUP, VLOOKUP, etc options and haven't had much luck since this is a goofy approximate matching sort of deal.

Currently, I have a workbook with two sheets. In one sheet, there's a column that contains a series of numbers with an underscore, various periods, and a varying amount of text. In another sheet, I have a column that contains the same numbers, but no text.

For example:

Column A

31383_1.1.mgs
32164_1.12 2.11 SR-0174

Column B

31383_1.1
32164_1.12

What I'm looking to do is build a formula that marks Column B as green (or whatever, just some formatting to indicate Yes) that, for example, both Column A and Column B contain 31383_1.1 despite the fact that Column A also contains the .msg text. I've ran through various Match, IF, XLOOKUP, VLOOKUP, etc options and haven't had much luck since this is a goofy approximate matching sort of deal.

• excel
• excel-formula

Share Improve this question Follow edited Mar 13 at 19:26 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 13 at 18:14 C3POvaryC3POvary 1544 bronze badges 2

• Will both columns have the same value in each row, or will you need to look in every row of Column B for a match from Column A? For example, if "31383_1.1.mgs" is at A2, will the "31383_1.1" be in B2? – Zack Commented Mar 13 at 18:47
• 2 =OR(ISNUMBER(FIND(B1,A:A))*NOT(ISBLANK(B1))) as a conditional formatting formula. A:A should be reduced in size to the smallest range necessary for efficiency. – Ron Rosenfeld Commented Mar 13 at 21:43

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Try this:

=IFERROR(AGGREGATE(15,6,ROW(OFFSET($A$1,0,0,COUNTA(A:A)))/((LEN(OFFSET($A$1,0,0,COUNTA(A:A)))-LEN(SUBSTITUTE(OFFSET($A$1,0,0,COUNTA(A:A)),B1,"")))<>0),1)>0,FALSE)

Meant to be applied starting from row 1. Column A must be a continuous.