excel - Index Match Using Wild Card References Issue In Reference Array

Question

I am looking to flag all line items in "array B" where a flag is assigned if there is a partial match in "array A". I would like to make the "flag" that is returned the cell found in "array A".

I am wondering whether or not Index Matching with a Wild Card reference is the correct way to accomplish this. I am pretty new to Excel formulas.

Please see below what I have already accomplished.

I have already tried multiple equations found on Stack Overflow, but they do not seem to address my issue. Please see below for the equation that I am trying to use.

view the screenshot of my file here:

Here is the formula I am trying to use =INDEX(B$1:B$9998,MATCH("*"&G2&"*",A$1:A$9999,0))

I expected the contents of the "flag" column to return but instead, the equation returns value #N/A

EDIT: I have included a simpler data set to use as an example below

View Simpler Data Set Here - Cant Post Images Yet - Edit to include if you can, thanks!

Answer 1

Thanks for your additional explanation. If you want to match the Variation Sku (column G) with the Master Sku (column A), I assume that you want to use wildcards because some of your variations (e.g. BER-92-MP-002) might slightly differ from column A (e.g. "xxxBER-92-MP-002xxx"), that is why you wanted to look for:

"*"&"BER-92-MP-002"&"*"

Assuming this is correct, then you can use an Array formula to look for the Row number where the match occurs, e.g. cell E2 (Ctrl+Shift+Enter):

=MAX(IFERROR(IF(FIND(G2,$A$2:$A$9),ROW($A$2:$A$9)),0))

Then your Flag can be retrieved as follows (cell F2):

=IF(E2,INDEX($B$1:$B$9,E2))

Screenshot with the final result:

I hope it helps & apologies if I misunderstood your original request. Happy to adjust both formulas if necessary (you can post additional screenshots by editing your original post).

Adjusted: Assuming that your search string always starts with "SKU" and is followed by "-" symbol and one additional string (e.g. SKU-BLUE), you can use the following formula in cell F2:

=IFERROR(MATCH(MID(H2,FIND("SKU-",H2),FIND("-",MID(H2,FIND("SKU-",H2),100),5)-1),$A$1:$A$5,0),0)

Formulas in column G are the same as in my previous post. Final result: