Another INDEX MATCH question

[juangregory]

Dear Excel gurus: i have a sheet that looks like this:

Department	Period	Hours Worked
Alpha	1	324
Alpha	2	421
Alpha	3	221
Beta	1	331
Beta	2	89
Beta	3	111
Gamma	1	0
Gamma	2	12
Gamma	3	212

And what I'm trying to do is to have another sheet do a lookup on a Field List with Alpha, Beta, Gamma, etc. and pull the Department, all periods, and all hours so that I can graph them. That way the end user can use the dropdown to see historical trending.

So let's say i have another sheet that has this:

(Dept pulldown)
Period	Hours
1
2
3
4

But =index('Sheet1!a:c',match($a$1,'Sheet1!a:a',0),match(a3,'Sheet1!b:b',0)) in the Hours column only gets me a #REF.

Is this because Period in Sheet1 is not a unique value? Is there a workaround?

Thank you in advance.

 

[Z51]

You can try this array formula (confirmed using CTRL+SHIFT+ENTER, not just ENTER). When done properly, you will see braces, {} around the formula in the formula bar. Don't add those yourself. Also, adjust the bottom row to a number that will always be greater than your last used row. Avoid using full columns, e.g. C:C, A:A, B:B in array formulas (and in many other cases).

=INDEX(Sheet1!$C$1:$C$20,MATCH($A$1&$A3,Sheet1!$A$1:$A$20&Sheet1!$B$1:$B$20,0))

 

[juangregory]

You can try this array formula (confirmed using CTRL+SHIFT+ENTER, not just ENTER). When done properly, you will see braces, {} around the formula in the formula bar. Don't add those yourself. Also, adjust the bottom row to a number that will always be greater than your last used row. Avoid using full columns, e.g. C:C, A:A, B:B in array formulas (and in many other cases).

=INDEX(Sheet1!$C$1:$C$20,MATCH($A$1&$A3,Sheet1!$A$1:$A$20&Sheet1!$B$1:$B$20,0))

Thank you pjoaquin that worked like a charm! you're a wizard! I don't think i've ever seen an example where concatenation was used in Index/Match and it never would have occurred to me to try it. You might have just saved me a whole saturday at work!

 

[Z51]

Don't tell your boss that... let them think you worked all day to get that done. (j/k)

 

[Peter_SSs]

You can try this array formula (confirmed using CTRL+SHIFT+ENTER, not just ENTER). When done properly, you will see braces, {} around the formula in the formula bar.

@pjoaquin @juangregory
Since the OP is using Microsoft 365, there is no need to use the Ctrl+Shift+Enter. Normal entry will do and the formula will not have the {} around it.

@juangregory
I'm not sure of your full data and its arrangement but based on your sample, you could try this shorter formula instead. Note that this does not need to be copied down, just entered in the top result cell.

juangregory.xlsm
	A	B
1	Beta
2	Period
3	1	331
4	2	89
5	3	111
6	4
Sheet2

Cell Formulas
Range	Formula
B3:B5	B3	=FILTER(Sheet1!C2:C20,Sheet1!A2:A20=A1,"")
Dynamic array formulas.

 

[Peter_SSs]

@juangregory
I have removed the 'Mark as Solution' mark from this thread. Please don't mark your own post as the solution unless it contains the solution that you provided. Please mark the actual post that best solves your problem.

 

[juangregory]

@Peter - thanks for the update; I suppose i should have read the rules for posting more closely. New here. Also, I wanted to thank you for pointing me in the direction of the Filter function. Just watched one of Leila Gharani's vids on the same thing and it has some interesting possibilities for another project I'm working on.