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

 

[AOB]

Cheers Steve - that's exactly what I'm doing - but the result returned is a zero-length string ("")

(See separate thread here - don't want to get pinged for cross-posting!!)

Even though, if I Debug.Print the value of the strFormula string variable and paste it into a cell in the sheet, it calculates correctly / as expected?

 

[AOB]

All solved - had two workbooks on the go and forgot that the other one was active at the time of the function call - solution posted on the other thread...