Excel help - match with wildcard

Factual Questions
1

I consider myself a pretty advanced Excel user but I’m having a probably and I haven’t been able to find a solution through Googling the usual sites.

I have a INDEX-MATCH formula. The MATCH piece is looking at the value of one cell ($AD3) and looking for a match in a range on another open workbook. It’s a long array formula but here is the relevant part:


MATCH(AD3,INDIRECT("'[PA Audit v2.xlsm]" & AF3 & "'!$X$1:$X$5000"),0)

The values in the target range are M,N,O,Y. The lookup value in AD3 might be Y, N, or * (wildcard). If the value is Y or N, this is working fine - but if the value in AD is an asterisk, I’m getting a “value not found” error. Any ideas why it’s not working as a wildcard?

2

Missed the edit window - but I’m having a problem, not a “probably.”

3

No idea. I tried it and it returned 1 when I had * in the cell. Your issue must be something else in the formula.

4

Huh. Strange it works with other values, but gives an error using the wildcard.

Anyway, I ended up short-circuiting the match if the lookup value is “". Just added an "If(AD3 = "”, true condition, MATCH(…)). I could do that since by definition, the wildcard matches everything anyway.