Wildcards with functions INDEX and EQUIV

  • Thread starter Thread starter Fabrice
  • Start date Start date
F

Fabrice

Hello everybody,

In my datasheet, i'm searching values with several conditions. The function is the following :

{=INDEX($J$2:$J$15024;MATCH(1;($J$2:$J$15024=N4)*($K$2:$K$15024=M4);0))}

The results of the function are OK. I would like to now if is it possible to add the wildcards * in the conditions. I've tried this ones but without success :

{=INDEX($J$2:$J$15024;EQUIV(1;($J$2:$J$15024=CHAR(42)&N4&CHAR(42))*($K$2:$K$15024=M4);0))}
{=INDEX($J$2:$J$15024;MATCH(1;($J$2:$J$15024="*"&N4&"*")*($K$2:$K$15024=M4);0))}

I don't no if is it possible, but if you have an idea...

Thank you in advance

Have a nice day
Fabrice
 
One way:

=INDEX($C$2:$C$7,MATCH(1,ISNUMBER(SEARCH(A1,$A$2:$A$7))*($B$2:$B$7=B1),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

I changed the ranges for testing and used commas instead of semicolon (USA
settings).

=INDEX($J$2:$J$15024;MATCH(1;isnumber(search(n4;$J$2:$J$15024))
*($K$2:$K$15024=M4);0))

(I think I got it all--but test it!)
 
Thank you very much it works very well.

Can I ask you another question. How can I insert this formula in several
ranges (for example S2:S7). I try this but it doesn't work :

1. I select the ranges S2:S7
2. I insert the following formula in the range S2 (I modifiy your formula) :


=INDEX($J$2:$J$15024;MATCH(1;isnumber(search(M2:P7;$J$2:$J$15024))*($K$2:$K$
15024=M2:M7);0))

3. And I validate with CTRL+SHIFT+ENTER

Do you know what I'm doing false ?

Another thanks for your help
Fabrice
 
I think it would be best to put it in S2 and drag down to S7.

I think single cell array formulas will think that they are multi-cell array
formulas if you try to do it all at once.
 
Back
Top