# An Old Favourite: INDEX and MATCH

#### AndyArry

##### New Member
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?

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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...

PHP:
``=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)

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!

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...

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?

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

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?

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

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.

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

taltyr

Replies
1
Views
273
Replies
3
Views
467
Replies
7
Views
468
Replies
5
Views
704
Replies
4
Views
475

1,203,485
Messages
6,055,686
Members
444,807
Latest member
RustyExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back