Wildcards with functions INDEX and EQUIV

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!)


> Fabrice wrote:
>
> 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

--

Dave Peterson

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

"Dave Peterson" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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!)
>
>
> > Fabrice wrote:
> >
> > 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
>
> --
>
> Dave Peterson

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.



Fabrice wrote:
>
> 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
>
> "Dave Peterson" <[email protected]> a écrit dans le message de
> news:[email protected]...
> > 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!)
> >
> >
> > > Fabrice wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

You're right.
It's perfekt, all works, thank you very much
Fabrice
"Dave Peterson" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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.
>
>
>
> Fabrice wrote:
> >
> > 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
> >
> > "Dave Peterson" <[email protected]> a écrit dans le message de
> > news:[email protected]...
> > > 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!)
> > >
> > >
> > > > Fabrice wrote:
> > > >
> > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson