Index/Match with Multiple rows/columns #NA error

**L4wd0g** · 12-23-2014, 11:57 PM

I've been stumped on this problem for a while now. I don't know a lot about excel, but have been trying to teach myself as I go to learn more. The Index/Match funcations have been my latest attempts at learning.

Here is my current problem. I'm creating a sheet that has a single column of employee names, a heading row of the different months, and then a table of #'s of widgets each person created during that month.

What I'm trying to do is create a list in the sheet that generates a top 10 list for each shift. I'm able to list the widgets from 1 to 10 with the Large function, but when I try to use the Index/Match functions to list the person's name along with the month, I'm getting the #NA error. From what I understand from reading various help sites, the Match function cannot handle multiple rows?

I figured maybe it's time for some real help. I'm new to the forum, I'll try to attach a sample. Any advice would be greatly appreciated!

**:) Sixthsense :)** · 12-24-2014, 01:03 AM

In D25 Cell

=INDEX($A:$A,SUMPRODUCT(MAX(($B$3:$M$12=C25)*ROW($B$3:$M$12))))

In E25 Cell

=INDEX($2:$2,SUMPRODUCT(MAX(($B$3:$M$12=C25)*COLUMN($B$3:$M$12))))

Drag both the formula's down...

**L4wd0g** · 12-24-2014, 01:54 AM

Worked great! Looks like you showed me my next excel topic to learn about!

Thanks again!

**FlameRetired** · 12-24-2014, 02:34 AM

It appears that you have multiple ties. I kept running into the same dilemma Sixthsense's formula produces....I couldn't
resolve the ties...names and months kept repeating. I've proposed a resolve that looks like this. File attached.

Row\Col	A	B	C	D	E
23		Ranks
24	Multi-Qualifiers	Day Shift		Person	Month
25	1	1	9844	Jim	Nov
26	1	2	9825	Phil	March
27	1	3	9435	Susie	July
28	1	4	5123	Bobby	April
29	2	5	3218	Raymond	March
30	2	5	3218	John	Nov
31	1	6	3121	Billy	Nov
32	1	7	1238	Phil	Dec
33	4	8	984	Jim	March
34	4	8	984	John	May
35	4	8	984	Phil	June
36	4	8	984	Steve	Sept
37	1	9	974	Jaylana	July
38	1	10	961	Raymond	June
39