IF, OR, VLOOKUP function not working with asterisk wildcard.

[JEH105]

I can't seem to figure this one out. I use this formula to search in other tables for multiple types of values. It always works like a charm, but, this year I've added two new categories "P-8" and "T-8" (the number 8 can range from 1-8, hence why i'm trying to use the asterisk) and it's not working with the asterisk. (In bold below) What am I doing wrong?

I'm having trouble with lines 5 and 6.

=IF(OR(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="U",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="H",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="J",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="C",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="P*",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="T*",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="B",
ISNUMBER(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0))),
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),"Available")

 

[Alex Blakenburg]

You can't use wildcards like that, try this:

=IF(OR(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="U",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="H",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="J",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="C",
Countif(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),"P*"),
Countif(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),"T*"),
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="B",
ISNUMBER(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0))),
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),"Available")

 

[JEH105]

You can't use wildcards like that, try this:

=IF(OR(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="U",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="H",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="J",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="C",
Countif(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),"P*"),
Countif(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),"T*"),
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="B",
ISNUMBER(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0))),
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),"Available")

Ahhhh! That answers my question. I really thought I could just use the wildcard with VLOOKUP like that.

I tried the COUNTIF, but for some reason it cancels out the rest of the formula... BUT I found an alternative:

=IF(OR(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="U",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="H",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="J",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="C",
LEFT(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),1)="P",
LEFT(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),1)="T",
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0)="B",
ISNUMBER(VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0))),
VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),"Available")

That did the trick!

Thank you for your time!

 

[Alex Blakenburg]

Thanks for letting us know. Glad you found a solution.

 

[Peter_SSs]

If you wanted something a bit shorter, I think this should do the same job for you?

=LET(v,VLOOKUP(J$4,FLOW4[[#All],[Date:]:[Daniel]],2,0),IF(OR(v={"U","H","J","C","B"},OR(LEFT(v,1)={"P","T"}),ISNUMBER(v)),v,"Available"))