# Index Match with Multiple Criteria

1. ## Index Match with Multiple Criteria

Hi,

I need to pull in numbers based on 3 multiple criteria. I have tried 3 variations of a formula, but all result in a value error. See below:

In my document (attached), I need to pull a number if there are matches between (1) the partner name in the top red box and the column of partner names (2) the software and the row of software, and (3) the "SUM" column for each software.

Formula 1 =INDEX(\$F\$9:\$I\$13, MATCH(\$C7, IF(AND(\$F\$6:\$I\$6="SUM",\$E\$9:\$E\$13=\$B\$2),\$F\$8:\$I\$8),0))

Formula 2 =INDEX(OFFSET(\$F\$9:\$F\$13,, MATCH(\$C8&"SUM",\$F\$8:\$AM\$8&\$F\$6:\$AM\$6, 0)-1), INDEX(F\$9:F\$13, MATCH(\$B\$2, \$E\$9:\$E\$13, 0))

Formula 3 =INDEX(\$F\$9:\$AM\$13,MATCH(\$C9&"SUM",\$F\$8:\$AM\$8&\$F6:\$AM\$6,0))

Thanks!

2. ## Re: Index Match with Multiple Criteria

I can't figure out what you want. Like, the MATCH is looking for something that doesnt exist; or you're looking in strange-sized arrays, or you're telling INDEX to get a matrix position but only giving it one coordinate instead of two.

What are the three criteria you're looking up against anyway? I only see one (the software package), although I guess the person ("Partner n") is the second, and even so, I have no idea what the third is.

3. ## Re: Index Match with Multiple Criteria

Hi Ben,

Thanks for reaching out. I have attached a document with color clarifications (the highlighted areas are the criteria). The things that need to be matched are the "SUM", Partner name, and software type.

Let me know if this helps.

Thanks!

4. ## Re: Index Match with Multiple Criteria

Where is SUM listed as a input? I mean, what you want is the "sum" column for each product, rather than the PR15etc stuff, right? Which cell lists that as the input?

Also your example output values don't match what you're telling me: you've got "Partner 1" listed as the person-under-consideration in C1 and B2:D2 bu then you have the "All Partners" values returned in your "what it should be" column. What does that mean?

5. ## Re: Index Match with Multiple Criteria

Perhaps if you included a few sample answers, it would help us understand what you want?

6. ## Re: Index Match with Multiple Criteria

Hi FDibbins,

In the original post, I have attached an updated document with sample answers and color codes to show matched areas.

Thanks,
C

7. ## Re: Index Match with Multiple Criteria

=INDEX(\$G\$8:\$AN\$20,MATCH(\$B\$2,\$F\$8:\$F\$20,0),MATCH(\$C7,\$G\$8:\$AN\$8,0))

8. ## Re: Index Match with Multiple Criteria

Actually, I it works!

Could you explain why this works without including the "Sum" in the index match? I need to replicate this "3 criteria" match on several different documents with different structures.

Thanks,
C

9. ## Re: Index Match with Multiple Criteria

I think the reason that the MATCH formula of FDibbins works without including "SUM" is that MATCH will find the FIRST cell that matches, searching from left to right. The first cell in this case is always in the SUM column.
I included the SUM in my formula since I figured you may need any of the other columns as well. However, that would require a yet more advanced formula because of the 15a, 15b etc.

Note that this is an array formula, needs to be entered with Ctrl + Shift + Enter.

edit: changed "right to left" to " left to right"

10. ## Re: Index Match with Multiple Criteria

Jacc is correct. I am basing that formula on you needing to find a value in the 1st of the 3 columns (because that's what your sample showed). If you need to find values in the other, was can modify that to do that, too. It would probably require an IF() statement that adds 1 or 2 to the 2nd MATCH()

11. ## Re: Index Match with Multiple Criteria

Hi Jacc,

Thank you so much! I was actually able to replicate it for another section that was similar. However, I was unable to edit it for still more criteria. If you have another second to help, I have attached the more difficult version.

In the attached, I have a list of codes and their frequency for various partners. I need to pull the top four codes and their corresponding counts and denominators. I have already used a RANK formula to determine which are the top codes for each partner (I want 1-4). Between the two worksheets, I need to match (1) Partner name (i.e., A B C D) (2) Question number (i.e., PO1, PO2) (3) Rank Number 1-4. I keep get #N/A or #VALUE, even when inputting as an array.

You are a saint, thanks again for all your help!

C

12. ## Re: Index Match with Multiple Criteria

Try this for a start...
=INDEX(Qual_Post!\$A\$1:\$V\$31,MATCH('Report Generator'!\$B\$2,Qual_Post!\$A\$1:\$A\$31,0),MATCH('Report Generator'!B\$3,Qual_Post!\$A\$1:\$V\$1,0))

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