# Index Match - Multiple Criteria - Multiple Results

1. ## Index Match - Multiple Criteria - Multiple Results

I know this is going to be extremely easy for all of you. For some reason I can put together these intricate workbooks (I just extracted what you needed to know for this problem), but I can't for the life of me learn this Row(\$A\$1:A1) thing (which I assume is what you'll tell me to use). I just can't figure it out!!!!

2. ## Re: Index Match - Multiple Criteria - Multiple Results

What are you trying to do?

Or, put another way: what are you wanting this to return?

=INDEX(Table1,MATCH(1,(\$K\$1=Table1[STORE])+("all"=Table1[STORE])*(\$K\$3 < Table1[EXPIRATION]),0),2)

The usual thing to do is provide a workbook with at least 10 rows of sample data and manually entered results to demonstrate what you are hoping for.

3. ## Re: Index Match - Multiple Criteria - Multiple Results

Wild guess:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(\$A\$2:\$A\$10)/(((\$A\$2:\$A\$10="All")+(\$A\$2:\$A\$10=\$K\$1))*(\$C\$2:\$C\$10>=\$K\$3)),ROWS(E\$8:E8))),"")

in E8, copied down.

4. ## Re: Index Match - Multiple Criteria - Multiple Results

Thanks sooo much, Glenn!!! You're a true rock star!

5. ## Re: Index Match - Multiple Criteria - Multiple Results

Do you understand it... even with the row thing (it's just a counter, BtW)?

If so, you're welcome. If not... ask.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

6. ## Re: Index Match - Multiple Criteria - Multiple Results

I really don't, and I don't know why. I understand almost every other area of excel after putting some time in to learn it. This just baffles me for some reason. I've got some vba I need to ask next, but I'm now learning that part.

7. ## Re: Index Match - Multiple Criteria - Multiple Results

If you type this into a cell:

=ROWS(E\$8:E8)

and drag copy down, you will see a list of results incrementing from 1 upwards.

In this case, as you drag down, the formula uses this as the AGGREGATE SMALL function counter.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

8. ## Re: Index Match - Multiple Criteria - Multiple Results

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(\$A\$2:\$A\$10)/(((\$A\$2:\$A\$10="All")+(\$A\$2:\$A\$10=\$K\$1))*(\$C\$2:\$C\$10>=\$K\$3)),ROWS(E\$8:E8))),"")

Red: if A2:A10 is equal to All or (the plus sign) K1
Orange: and C2:C10 is less than K3,
Cyan: return the row number
Green: in ascending order
Blue: starting with 1, then 2, etc,
Purple: returning as the final result the corresponding value in column B
Black: if error returned, return a blank.

9. ## Re: Index Match - Multiple Criteria - Multiple Results

Orange: and C2:C10 is less than K3,

10. ## Re: Index Match - Multiple Criteria - Multiple Results

Duhh. >=K3

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