INDEX / MATCH with wildcard (XL2007)

[AOB]

Hi guys,

I've read online that it is possible to use wildcard characters within INDEX / MATCH array formulas but I'm having no luck implementing such a formula?

I have a sheet of data and need to locate information in a named range ("rngLookup") based on 4 criteria - one of which is a 'partial' match of text. So in one column (column B), I have some pipe-delimited 'options' and I need the formula to detect a match if the keyword is present within the cell's text, enclosed by the pipe character.

This is the formula I'm using :

{=IFERROR(INDEX(rngLookup,
  MATCH(1,
    (OFFSET(rngLookup,0,0,,1)="Alan")*
    [COLOR=#ff0000](OFFSET(rngLookup,0,1,,1)="*|DEFG|*")*[/COLOR]
    (OFFSET(rngLookup,0,2,,1)="123")*
    (OFFSET(rngLookup,0,4,,1)="X")
  ,0),4),
"")}

This is the line in the named range that should match based on the above example (and return the "Y" in column D)

	A	B	C	D	E	F
1	Alan	|ABCD|DEFG|	123	Y	X	98.76

<tbody>
</tbody>

The second array (highlighted in red) is returning all FALSE values; I was hoping the wildcards before and after in the string could be used to detect the required text within the cell?

Can anybody advise what I'm doing wrong? (The other 3 criteria are working fine...)

Thanks

AOB

 

[steve the fish]

What is the range address of rngLookup?

 

[AOB]

It's a dynamic range but basically it's columns A1:F215

(Could extend downward to more rows over time but will always be columns A:F)

 

[steve the fish]

Actually thinking about it this should work:

=IFERROR(INDEX(rngLookup,MATCH(1,(OFFSET(rngLookup,0,0,,1)="Alan")*ISNUMBER(SEARCH("|DEFG|",OFFSET(rngLookup,0,1,,1)))*(OFFSET(rngLookup,0,2,,1)=123)* (OFFSET(rngLookup,0,4,,1)="X"),0),4),"")

 

[AOB]

Works a charm! Cheers Steve!

(So I take it that wildcards aren't actually an option in INDEX / MATCH array formulas then?...)

 

[Mackers]

Works a charm! Cheers Steve!

(So I take it that wildcards aren't actually an option in INDEX / MATCH array formulas then?...)

I know that you can use wildcards in MATCH() for the lookup_value. I'm not sure about in the lookup_array, I haven't tried that.

=MATCH("*"&A1&"*", lookup_array, 0)

The above will find the first cell in the lookup_array that has a string containing the text A1 anywhere within it, so if A1 was the letter "d" it would find the first cell containing a letter d.

Hope that helps

Mackers

 

[steve the fish]

Yes maybe it was more that offset didn't like it rather than match. Match is happy with wildcards. This for instance would work:

=MATCH("*|DEFG|*",B9:B14,0)

 

[steve the fish]

This is slightly different way of saying the same thing without the OFFSET.

=IFERROR(INDEX(rngLookup,MATCH(1,IF(INDEX(rngLookup,,1)="Alan",IF(ISNUMBER(SEARCH("*|DEFG|*",INDEX(rngLookup,,2))),IF(INDEX(rngLookup,,3)=123,IF(INDEX(rngLookup,,5)="X",1)))),0),4),"")

 

[AOB]

Thanks for all the help guys!

I can get the formula working fine now, however my next step is to try to do this through VBA rather than directly onto the spreadsheet.

I'll start a new thread as technically it's a different problem...

 

[steve the fish]

You could use evaluate.

x = Evaluate("=IFERROR(INDEX(rngLookup,MATCH(1,IF(INDEX(rngLookup,,1)=""Alan"",IF(ISNUMBER(SEARCH(""*|DEFG|*"",INDEX(rngLookup,,2))),IF(INDEX(rngLookup,,3)=123,IF(INDEX(rngLookup,,5)=""X"",1)))),0),4),"""")")
MsgBox x