INDEX/MATCH (multiple critera in multiple rows and columns)

1. INDEX/MATCH (multiple critera in multiple rows and columns)

I have a large amount of data that I need to sum in various ways. I have no control over the format of the spreadsheet that contains the data as I receive it from an outside source and need to provide inputs back in this same format. I would like to find a way to write a formula that will return the value in the cell based on multiple criteria. I have two criteria columns and also two header rows that are criteria. I've attached a very simplified version of how the data I receive is formatted. My criteria is in cells A16:A20. I can't seem to hit the right combination of formulas to pull the value from the full range (C3:P11) that matches that criteria. Any help would be appreciated.

2. Re: INDEX/MATCH (multiple critera in multiple rows and columns)

=INDEX(C3:P11,MATCH(1,IF(A3:A11=A17,IF(B3:B11=A18,1)),0),MATCH(1,IF(C1:P1=A19,IF(C2:P2=A20,1)),0))

IMPORTANT
This is an array formula
Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually

3. Re: INDEX/MATCH (multiple critera in multiple rows and columns)

Try this formula:

=SUMPRODUCT((\$A\$3:\$A\$11=A17)*(\$B\$3:\$B\$11=A18)*(\$C\$1:\$P\$1=A19)*(\$C\$2:\$P\$2=A20),\$C\$3:\$P\$11)

Hope this helps.

Pete

4. Re: INDEX/MATCH (multiple critera in multiple rows and columns)

Both work perfectly. I knew there was something simple I was missing - thanks so much.

6. Re: INDEX/MATCH (multiple critera in multiple rows and columns)

I have a similar problem, but the formula did not work for me. I have attached a sample spreadsheet. I need to Match the PIN numbers on the Detail and Payroll and have the totals from the Payroll entered on the correct column of the detail. There will be PIN numbers that do not match or are not listed on the Detail spreadsheet. I also need some kind of formula to highlight the discrepancies.

7. Re: INDEX/MATCH (multiple critera in multiple rows and columns)

@ccwynar:

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

8. Re: INDEX/MATCH (multiple critera in multiple rows and columns)

OK, I am new at this I will open a new thread.

10. Re: INDEX/MATCH (multiple critera in multiple rows and columns)

lookup value is 9 and show result

JOPITER DD231
ALPHA DD232

9 JOPITER DD231 D.RED 3.96 25
9 ALPHA DD232 BLACK 3.96 25
9 BRAWO DD233 BLUE 3.96 22
9 MAJEDA DK234 GREEN 3.96 21

how is it posible

if one value is repeated which formula we use

11. Re: INDEX/MATCH (multiple critera in multiple rows and columns)

@ehtishanhussain

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

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