Referencing Value in "Column Groups" (INDEX MATCH..?)

[Seansy]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Jan
[/TD]
[TD]Jan
[/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Feb
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Mar
[/TD]
[TD]Mar
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Subscribers
[/TD]
[TD]Leads
[/TD]
[TD]Customers
[/TD]
[TD]Subscribers
[/TD]
[TD]Leads
[/TD]
[TD]Customers
[/TD]
[TD]Subscribers
[/TD]
[TD]Leads
[/TD]
[TD]Customers
[/TD]
[/TR]
[TR]
[TD]Homer
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Lenny
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Carl
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]Barney
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]











Hi everyone,

I'm stuck on a project and may be missing something obvious.

I need a formula to reference, e.g., how many customers Homer had in January. Imagine a VLOOKUP where the column number would be the number when the month and KPI match. The data would be in Worksheet A and the formula would be in Worksheet B where the use can select the Employee and Month in question (there would already be a section to display the KPIs).

Does this make sense and can this be done? My research is leading me towards an INDEX MATCH formula, but I'm not sure if that's the right option for me. I'll post an update if I figure this one out by myself.

Thank you

 

[steve the fish]

Some options:

=INDEX(B3:J6,MATCH("Homer",A3:A6,0),MATCH(1,INDEX((B1:J1="Jan")*(B2:J2="Customers"),0),0))
=SUMPRODUCT((B1:J1="Jan")*(B2:J2="Customers")*(A3:A6="Homer")*B3:J6)
=SUMIFS(INDEX(B3:J6,0,MATCH(1,INDEX((B1:J1="Jan")*(B2:J2="Customers"),0),0)),A3:A6,"Homer")
=VLOOKUP("Homer",A3:J6,MATCH(1,INDEX((A1:J1="Jan")*(A2:J2="Customers"),0),0),0)

 

[Kamolga]

Hi,

The simplest way is to add a row that concatenate both title on the top (then hide row or write in wite). So Homer ends up in A4. B1 formula to concatenate is

=B2&B3

that you drag all yhe way to the right

You can then do an index match in worksheet B.

In WorksheetB, if A1 is Jan, B1 is Homer and C1 is Customers (you can have dropdown lists)

Then index match formula is

=INDEX('Workshet A'!A1:J7,MATCH('Worksheet B'!B1,'Workshet A'!A1:A7,0),MATCH('Worksheet B'!A1&'Worksheet B'!C1,'Workshet A'!A1:J1,))

and returns 7

You can download the file here

https://1drv.ms/x/s!AvmGsNl7aaaAgvQwRLfy9Bztj_lLDA

 

[Kamolga]

Basically I would add a row on top of table in Worksheet A to concatenate both titles. I would use white font or hide the row then.

[/URL][/IMG]

In Worksheet B, I would use this formula

[/URL][/IMG]

=INDEX('Workshet A'!A1:J7,MATCH('Worksheet B'!B1,'Workshet A'!A1:A7,0),MATCH('Worksheet B'!A1&'Worksheet B'!C1,'Workshet A'!A1:J1,))

You can downlad my test file here: https://1drv.ms/x/s!AvmGsNl7aaaAgvQwRLfy9Bztj_lLDA

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Mar[/TD]
[TD]Mar[/TD]
[TD]Sheet A[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]Subscribers[/TD]
[TD]Leads[/TD]
[TD]Customers[/TD]
[TD]Subscribers[/TD]
[TD]Leads[/TD]
[TD]Customers[/TD]
[TD]Subscribers[/TD]
[TD]Leads[/TD]
[TD]Customers[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Homer[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Lenny[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Carl[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD]

11​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Barney[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Subscribers[/TD]
[TD]Sheet B[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Homer[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Lenny[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Carl[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Barney[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet B

B2=SUMPRODUCT(('Sheet A'!$A$3:$A$6='Sheet B'!$A2)*('Sheet A'!$B$1:$J$1='Sheet B'!$B$1)*('Sheet A'!$B$2:$J$2='Sheet B'!$C$1)*('Sheet A'!$B$3:$J$6)) copy down


for another month change B1 with Feb or Mar

for another role change C1 Subscribers with Leads or Customers

 

[Seansy]

Thank you for these excellent solutions! I opted for steve's VLOOKUP solution, but all of these formulas are an excellent reference for the future.