Two Column Look Up the return value

[AliaNi]

I am trying to find a formula which looks up the day first (Row two) and then looks up the metric row (Row three) and then retur the average of that column for me.

I tried installing Xl2bb but it won't work on my computer.

How do you do that? I ran out of all ideas.

 

[awoohaw]

in the example you provide what result do you expect? both row 2 and row 3 are blank.

 

[AliaNi]

in the example you provide what result do you expect? both row 2 and row 3 are blank.

I created another table. I added the expected results, but I want to be able to do that using formulas and xlookup or any other way for when I don't know the size of the datasets.

I have been trying to do it for a while now but it's not working.

For example, the crossing of Sun and Metric 1 should yield 49 (The median for that column).

Book1
	A	B	C	D	E	F	G
1		Sunday			Monday
2	ID	Metric 1	Metric 2	Metric 3	Metric 1	Metric 2	Metric 3
3	2	33	54	55	23	75	84
4	3	23	33	33	33	98	72
5	4	65	22	88	44	65	77
6	1	92	11	33	55	33	56
7
8	expected results	49	27.5	44	38.5	70	74.5
9
10		Metric 1	Metric 2	Metric 3
11	Sunday
12	Monday
Sheet1

Cell Formulas
Range	Formula
B8:G8	B8	=MEDIAN(B3:B6)

 

[awoohaw]

This is not ultra dynamic, you have to adjust ranges in the first calculation and then copy over and down.

Also, you have an extra space at the end of Sunday.

WorkBook1.xlsx
	A	B	C	D	E	F	G
1		Sunday			Monday
2	ID	Metric 1	Metric 2	Metric 3	Metric 1	Metric 2	Metric 3
3	2	33	54	55	23	75	84
4	3	23	33	33	33	98	72
5	4	65	22	88	44	65	77
6	1	92	11	33	55	33	56
7
8	expected results	49	27.5	44	38.5	70	74.5
9
10		Metric 1	Metric 2	Metric 3
11	Sunday	49	27.5	44
12	Monday	38.5	70	74.5
Sheet5

Cell Formulas
Range	Formula
B8:G8	B8	=MEDIAN(B3:B6)
B11:D12	B11	=MEDIAN(INDEX($B$3:$G$6,1, MATCH($A11,$B$1:$G$1,0) +MATCH(B$10,$B$2:$D$2,0)-1):INDEX($B$3:$G$6,ROWS($A$3:$A$6), MATCH($A11,$B$1:$G$1,0) +MATCH(B$10,$B$2:$D$2,0)-1))

 

[awoohaw]

And, I'm happy you got the xl2bb add in working!

How will you be adding data into this worksheet? Always down, always to the right, or both?
Is there a lot of data? The structure of your data at the top is not very conducive to analysis if you can change that it would be great. If you only add new rows, Power Query (An add-in for 2010 excel, I think) is a great tool that can update data as you add.

If you add columns, that is more difficult.
.

 

[AliaNi]

How will you be adding data into this worksheet? Always down, always to the right, or both?
Is there a lot of data? The structure of your data at the top is not very conducive to analysis if you can change that it would be great. If you only add new rows, Power Query (An add-in for 2010 excel, I think) is a great tool that can update data as you add.

If you add columns, that is more difficult.
.

So I will have 7 columns but it's the number of rows which keeps changing. But I do know that the number will not be exceeding a certain number so I guess it should work. It is now returning N/As I have to check what I did wrong.

 

[AliaNi]

And, I'm happy you got the xl2bb add in working!

How will you be adding data into this worksheet? Always down, always to the right, or both?
Is there a lot of data? The structure of your data at the top is not very conducive to analysis if you can change that it would be great. If you only add new rows, Power Query (An add-in for 2010 excel, I think) is a great tool that can update data as you add.

If you add columns, that is more difficult.
.

 

[awoohaw]

you need to let me see your formula

 

[AliaNi]

you need to let me see your formula

Book1
	A	B	C	D	E	F	G
1		Sunday			Monday
2	ID	Metric 1	Metric 2	Metric 3	Metric 1	Metric 2	Metric 3
3	2	33	54	55	23	75	84
4	3	23	33	33	33	98	72
5	4	65	22	88	44	65	77
6	1	92	11	33	55	33	56
7
8	expected results	49	27.5	44	38.5	70	74.5
9
10		Metric 1	Metric 2	Metric 3
11	Sunday	#N/A	#N/A	#N/A
12	Monday
Sheet1

Cell Formulas
Range	Formula
B8:G8	B8	=MEDIAN(B3:B6)
B11:D11	B11	=MEDIAN(INDEX($B$3:$G$6,1, MATCH($A11,$B$1:$G$1,0) +MATCH(B$10,$B$2:$D$2,0)-1):INDEX($B$3:$G$6,ROWS($A$3:$A$6), MATCH($A11,$B$1:$G$1,0) +MATCH(B$10,$B$2:$D$2,0)-1))

 

[awoohaw]

You still have the space after Sunday in cell A11