VLookup all occurrences of value to get average

[cgcamal]

Hi to all,

Hoping somebody could help me.

I have this table:

Excel Workbook
	A	B	C	D	E	F
1	2	3				4.16666667
2
3	1	3	1	2	2
4	3	5	3	1	-3
5	4	2	5	0	2
6	5	1	7	-1	1
7	2	7	9	-2	3
8	1	4	11	-3	1
9	3	2	13	-4	6
10	5	7	15	-5	3
11	11	2	17	-6	4
12	3	4	19	-7	2
13	2	1	21	-8	9
14	8	2	23	-9	1
15	5	9	25	-10	2
...

And I want to look up, in columns A and B, the value in A1=2, but I need something like an extension of "VLookup"
to get all values in Column E (values in green) in the same row of each "2" found and obtain the the average of
those values in green that is 4.16666667 as in cell F1.

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,A3:E15,5,FALSE)
F1	=AVERAGE(E5,E7,E9,E11,E13,E14)

The issue with VLOOKUP is that returns only the first match for first value in column A. I think an array formula will do
the trick, because it could be many matches, not only 6 as in the sample.

Thanks in advance for any help.

Regards,

 

[Ron Coderre]

Using your posted example,
this regular formula returns the average of Col_E cells
where the corresponding Col_A OR Col_B cell equals 2.

F1: =SUMPRODUCT((A3:B15=2)*E3:E15)/COUNTIF(A3:B15,2)

Note: that formula assumes that the number 2 will NOT occur twice in the same row.

Is that something you can work with?

 

[cgcamal]

Hey Ron,

Thanks for your help. Much more simpler than I was thinking and trying to do without success.

That works perfect, because the value "2" only occurs once in the same row.

Many thanks again.

Best regards

 

[T. Valko]

Hi to all,

Hoping somebody could help me.

I have this table:

#VALUE!

#VALUE!

And I want to look up, in columns A and B, the value in A1=2, but I need something like an extension of "VLookup"
to get all values in Column E (values in green) in the same row of each "2" found and obtain the the average of
those values in green that is 4.16666667 as in cell F1.

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,A3:E15,5,FALSE)
F1	=AVERAGE(E5,E7,E9,E11,E13,E14)

The issue with VLOOKUP is that returns only the first match for first value in column A. I think an array formula will do
the trick, because it could be many matches, not only 6 as in the sample.

Thanks in advance for any help.

Regards,

Here's another one...

Array entered**:

=AVERAGE(IF((A3:A15=A1)+(B3:B15=A1),E3:E15))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[cgcamal]

Hi T. Valko,

I've tested it and works correctly too.

Many thanks for your help.

Best regards.

 

[T. Valko]

Hi T. Valko,

I've tested it and works correctly too.

Many thanks for your help.

Best regards.

You're welcome. Thanks for the feedback!

 

[Ron Coderre]

Glad to help.