An Old Favourite: INDEX and MATCH

[AndyArry]

OK, I'm a noob so, first up, I want to say what a great site this is, been using it for ages and it never fails to solve my problems.....

... until now


I have a 'challenge' and it looked like the answer was a flavour of the old
=INDEX(...,MATCH(1,........))
lookup variant but I just can't get it to work

Challenge
This is a membership database for my local sports club, with one large worksheet 'Membership Details', containing all the pertinent info;
- column A holds an alphabetical list of surnames
- column C holds text defining the type of membership ('SeniorM', 'JuniorF', etc....)
- column AK holds a 'y' if they've paid their annual membership and an 'n' if not.

I want to, on a separate worksheet, list all those members with a particular membership type, that have paid their subs, in alphabetical surname sequence.

I thought
{=INDEX('Membership Details'!$A$2:$A$391,MATCH(1,('Membership Details'!$AK$2:$AK$391="y")*('Membership Details'!$C$2:$C$391="SeniorM"),0))}
would work but, alas, this seems to return different subsets of the names, in odd sequences, not limited to 'SeniorM' members....

Can anyone point out the glaring error that I'm making, please?

 

[njimack]

Welcome to the board.

There may be easier ways, but the below should work for you - enter into row 2 of a separate sheet, and then copy down as far as required. It will return #NUM! when it runs out of names to list...

=INDEX('Membership Details'!$A$2:$A$20,SMALL(IF('Membership Details'!$C$2:$C$20="SeniorM",IF('Membership Details'!$AK$2:$AK$20="Y",ROW('Membership Details'!$A$2:$A$20)-1)),ROW()-1))

(note: confirm with ctrl+shift+enter)

 

[AndyArry]

njimack,

Eureka, it works!
I haven't gone through the formula to understand it yet, but it works, so I'm a happy noobie

Thanks!

 

[AndyArry]

I'm getting there: I've added an 'ISERROR' check to get rid of the #NULL returns

But, onwards and upwards ....

Supposing I now wanted to find all the names (from Column A) where:
- the type of membership (in column C) is either 'SeniorM', 'ColtM' or 'StudentM'
and
- the paid indicator (column AK) is 'y'

Using nested IFs doesn't seem to work...

 

[njimack]

You might be better with a VBA approach that uses autofilter to retrieve the relevant rows. Anyway, here's a formula approach:

Excel Workbook
	A	B	C	D
2	Name1			TYPE
3	Name5			SeniorM
4	Name9			ColtM
5	Name13			StudentM
6	Name17
7	#NUM!
8	#NUM!
9	#NUM!
10	#NUM!
11	#NUM!
12	#NUM!
Sheet2

 

[njimack]

By the way, rather than wrapping the entire function in ISERROR, why not just use conditional formatting so that the result turns white if it returns an error?

 

[AndyArry]

By the way, rather than wrapping the entire function in ISERROR, why not just use conditional formatting so that the result turns white if it returns an error?

OK, OK, the formulas are fantastic but that one is just taking the mickey

Thanks, njimack!!!

 

[zookeepertx]

By the way, rather than wrapping the entire function in ISERROR, why not just use conditional formatting so that the result turns white if it returns an error?

Hi there - (just reading over your shoulders here, LOL.)

I'm not sure what you mean with the above part. Wouldn't that still leave the error in the cell? What if there's another formula that relies on that cell having either a number or being empty? (I'm working on an INDEX/MATCH formula & tried using ISERROR, but it wouldn't work for me, so I'm hoping for an alternative.)
Thanks!

Jenny

 

[njimack]

Hi there - (just reading over your shoulders here, LOL.)

I'm not sure what you mean with the above part. Wouldn't that still leave the error in the cell? What if there's another formula that relies on that cell having either a number or being empty? (I'm working on an INDEX/MATCH formula & tried using ISERROR, but it wouldn't work for me, so I'm hoping for an alternative.)
Thanks!

Jenny

Yes, it would leave the error, but it wouldn't be visible (I assumed the purpose was simply to list the relevant data, rather than linking to it).

I'm personally not a fan of wrapping functions in ISERROR because it means carrying out the function twice. The best approach to this specific question would have been VBA. Alternatively, we could write a function to count how many rows meet the criteria, and then build that into the function that lists the data, so that any excess rows simply display as blanks.

 

[taltyr]

I may be missing something, but would a Pivot Table not give you all the searching/reporting functionality you need.

taltyr