Index and Match results

[Mick]

I have used the index / match formula similar to below but it only returns
the first instance of the match, is there a way of having all the items
listed in column A that matches the criteria rather than just the first one
it comes across.

Thoughts were that on a seperate worksheet I copied the formula down to row
100, each formula starting at the row number it was actually in, this would
give me a list that meets the criteria (with duplicate names) but how would
I copy this this back to my original worksheet without the duplicate names.

=INDEX(Sheet2!A1:A100,MATCH(1,(Sheet2!B1:B100>"3")*(Sheet2!C1:C100=1234),0))

Many thanks
Mick

 

[RagDyeR]

Try this *array* formula:

=INDEX(A$1:A$100,SMALL(IF((B$1:B$100>"3")*(C$1:C$100=1234),ROW($1:$100)),ROWS($1:1)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE, copy down as needed ... more rows then you think you'll
need ... so that you don't run out of rows of formula before you run out of
data to be returned.

When you run out of data, you'll get the #NUM! error.

If the error message bothers you, you can try this, where the error trap
makes the formula kind of bigger:

=IF(ISERR(SMALL(IF((B$1:B$100>"3")*(C$1:C$100=1234),ROW($1:$100)),ROWS($1:1))),"",INDEX(A$1:A$100,SMALL(IF((B$1:B$100>"3")*(C$1:C$100=1234),ROW($1:$100)),ROWS($1:1))))

This must *also* use CSE!

I eliminated your path to Sheet2 to make it shorter for my testing.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have used the index / match formula similar to below but it only returns
the first instance of the match, is there a way of having all the items
listed in column A that matches the criteria rather than just the first one
it comes across.

Thoughts were that on a seperate worksheet I copied the formula down to row
100, each formula starting at the row number it was actually in, this would
give me a list that meets the criteria (with duplicate names) but how would
I copy this this back to my original worksheet without the duplicate names.

=INDEX(Sheet2!A1:A100,MATCH(1,(Sheet2!B1:B100>"3")*(Sheet2!C1:C100=1234),0))

Many thanks
Mick

 

[RagDyeR]

BTW ... Are you *sure* that you want the quotes around that "3" ? ? ?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Try this *array* formula:

=INDEX(A$1:A$100,SMALL(IF((B$1:B$100>"3")*(C$1:C$100=1234),ROW($1:$100)),ROWS($1:1)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE, copy down as needed ... more rows then you think you'll
need ... so that you don't run out of rows of formula before you run out of
data to be returned.

When you run out of data, you'll get the #NUM! error.

If the error message bothers you, you can try this, where the error trap
makes the formula kind of bigger:

=IF(ISERR(SMALL(IF((B$1:B$100>"3")*(C$1:C$100=1234),ROW($1:$100)),ROWS($1:1))),"",INDEX(A$1:A$100,SMALL(IF((B$1:B$100>"3")*(C$1:C$100=1234),ROW($1:$100)),ROWS($1:1))))

This must *also* use CSE!

I eliminated your path to Sheet2 to make it shorter for my testing.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have used the index / match formula similar to below but it only returns
the first instance of the match, is there a way of having all the items
listed in column A that matches the criteria rather than just the first one
it comes across.

Thoughts were that on a seperate worksheet I copied the formula down to row
100, each formula starting at the row number it was actually in, this would
give me a list that meets the criteria (with duplicate names) but how would
I copy this this back to my original worksheet without the duplicate names.

=INDEX(Sheet2!A1:A100,MATCH(1,(Sheet2!B1:B100>"3")*(Sheet2!C1:C100=1234),0))

Many thanks
Mick

 

[Mick]

Many many thanks for your time and assistance to you both.
It worked well, I even added another criteria to the formula (but did not
chnage anything else ie row numbers) and that worked as well.

Thanks again
Mick