Re: What to use instead of wildcards in excel IF statements

On May 5, 1:31 am, Harlan Grove <hrln...@xxxxxxxxx> wrote:
Lil <typic...@xxxxxxxxx> wrote...
I want to pick up data for groups that have have numeric and alpha
codes, regardless of the alpha ending.
Wildcard works in the VLOOKUP but not in the IF scenario.

Here's the current formula:

Current formula:
=IF('[path.xls]Groups ***'!F68="22-950A",
(VLOOKUP("22-950A",'[path.xls]Groups ***'!F68:$U$418,5,FALSE))," ")

Meaning your VLOOKUP call would look more like

VLOOKUP("22-950*",'[path.xls]Groups ***'!F68:$U$418,5,FALSE)

and you want something similar for the IF? If so, try

=IF(LEFT('[path.xls]Groups ***'!F68,6)="22-950",
VLOOKUP("22-950A",'[path.xls]Groups ***'!F68:$U$418,5,0),"")

Note that I also removed unnecessary parentheses, shortened the
VLOOKUP call by using 0 instead of FALSE as 4th argument, and changed
the no-match result to "". The last is the most significant change. ""
works much better as a no-result return value than " " because, for
example, COUNTBLANK treats "" like truly blank/empty cells but treats
" " as nonblank.

It worked - as you knew it would. Thank you so much Harlan Grove, may
your path be scattered with fragrant petals, or cartons of Fosters,
whichever is your calling.
(must tell all those folk I badgered & who were also found to be
clueless)
Lil



.