Index Match with multiple criteria

1. Index Match with multiple criteria

Okay I've been searching on how to do this and I'm just not getting it correct. This webpage seems to be doing what I want to do. However my formula isn't coming out right.

Basically I have to sheets. One contains active people and the other contains every ID those people have. So the other sheet would have multiple rows of the active people each containing a different ID for them. I'm only interested in two forms of those ID's. The one's starting with "A" and the ones starting with "B".

Some people do not have any ID and others have an A and B ID and maybe a C or D ID as well. The formula I'm using is:
{=IFERROR(INDEX(Sheet2!H:H,MATCH(1,(A3=Sheet2!A:A)*(Sheet2!H:H="%A%"),0)),"")}

And it's just not working.

On the test sheet, the formula is:
{=IFERROR(INDEX(Sheet2!B:B,MATCH(1,(A2=Sheet2!A:A)*(Sheet2!B:B="%A%"),0)),"")}

Where am I going wrong?

2. Re: Index Match with multiple criteria

It might have been better to give us your (manually calculated) exppected answers rather than a non-working formula. They rarely point us in the right direction:

=IFERROR(INDEX(Sheet2!\$B:\$B,AGGREGATE(15,6,ROW(Sheet2!\$B\$2:\$B\$100)/((Sheet2!\$A\$2:\$A\$140=Sheet1!\$A2)*(LEFT(Sheet2!\$B\$2:\$B\$140,1)={"A","B"})),COLUMNS(\$B:B))),"")

see sheet.

3. Re: Index Match with multiple criteria

Thanks - I was way off. I will keep that in mind for the future.

4. Re: Index Match with multiple criteria

You're welcome!!

5. Re: Index Match with multiple criteria

Originally Posted by Glenn Kennedy

=IFERROR(INDEX(Sheet2!\$B:\$B,AGGREGATE(15,6,ROW(Sheet2!\$B\$2:\$B\$100)/((Sheet2!\$A\$2:\$A\$140=Sheet1!\$A2)*(LEFT(Sheet2!\$B\$2:\$B\$140,1)={"A","B"})),COLUMNS(\$B:B))),"")
Just a FMI question Glenn, I normally use the INDEX SMALL ROW INDEX formula for this, how do you extend your formula, which as it stands in the example posted collects data from two columns, across 3 or more columns?

6. Re: Index Match with multiple criteria

I'm not quite sure what you mean. Can you mock up a sample and post in a new thread? PM me to alert me to the fact that you have posted and I'll take a look asap thereafter.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1