Wildcards & Text in Index(Match ???????

[spydey]

Trying to use index(match to bring over some data from one sheet to another one.

Part of the criteria is based upon text found in a string. I was hoping to just use wild cards but it appears, after trying a few times, that it isn't working.

Where am I going wrong?

On sheet1 in cell D2 I am inputting the following:

=INDEX(SHEET2NAMEDRANGE1,MATCH(1,(SHEET2NAMEDRANGE2=A2)*(SHEET2NAMEDRANGE3="*ABC*")*(SHEET2NAMEDRANGE4="*XYZ*"),0),1)


SHEET2NAMEDRANGE1 is on sheet2 and is a single column, column F, it is a date.

SHEET2NAMEDRANGE2 is on sheet2 and is a single column, column A, it is numeric.

SHEET2NAMEDRANGE3 is on sheet2 and is a single column, column G, it is a text string.

SHEET2NAMEDRANGE4 is on sheet2 and is a single column, column D, it is a text string.

I get #N/A when I run confirm it with CSE.

Any thoughts?

-Spydey

P.S. Hmmm, just thinking through this, I wonder if I need to use something like .... (SHEET2NAMEDRANGE3=ISNUMBER(SEARCH("*ABC*",SHEET2NAMEDRANGE3))) ...... but something tells me that isn't right because I would be using a named range in my SEARCH( range ..... I think .....

 

[wideboydixon]

You can't use a wildcard search like that I don't think. You could probably re-write it as:

=INDEX(SHEET2NAMEDRANGE1,MATCH(1,(SHEET2NAMEDRANGE2=A2)*(ISNUMBER(FIND("ABC",SHEET2NAMEDRANGE3)))*(ISNUMBER(FIND("XYZ",SHEET2NAMEDRANGE4))),0))

WBD

 

[spydey]

You can't use a wildcard search like that I don't think. You could probably re-write it as:

=INDEX(SHEET2NAMEDRANGE1,MATCH(1,(SHEET2NAMEDRANGE2=A2)*(ISNUMBER(FIND("ABC",SHEET2NAMEDRANGE3)))*(ISNUMBER(FIND("XYZ",SHEET2NAMEDRANGE4))),0))

WBD

Sweet! Thanks WBD. I will give that a try.

-Spydey

 

[spydey]

WBD,

Thanks for the push in the right direction. I changed it a smidge and it is working now. Thanks man!

My formula is as follows:

=INDEX(SHEET2NAMEDRANGE1,MATCH(1,(SHEET2NAMEDRANGE2=A2)*(ISNUMBER(SEARCH("*ABC*",SHEET2NAMEDRANGE3)))*(ISNUMBER(SEARCH("*XYZ*",SHEET2NAMEDRANGE4))),0))

It actually froze for like 30 - 45 seconds as it performed the calculations on approx 1k cells that I filled it down on. But it came back correctly!!

Thanks again!!

-Spydey