Return median value array in google sheets

Question

I'm trying to match the value in an array and return the median value found in the fourth column of an array in another sheet B_Sheet!B1:B65404

Since Vlookup only returns the first value I tried using Index and Match but not sure why it failed.

Tried this but didnt work ARRAYFORMULA(IFERROR(MEDIAN(FILTER(INDEX(B_Sheet!D1:D65404,MATCH(B5:B36518,B_Sheet!B1:B65404,0)),LEN(INDEX(B_Sheet!D1:D65404,MATCH(B5:B36518,B_Sheet!B1:B65404,0)))>0)),""))

For reference, this Vlookup did work:

ARRAYFORMULA(IFERROR(VLOOKUP(B5:B36518,B_Sheet!B1:E65404, 4, FALSE),""))

Example1: With formula in top of second column of Sheet1

Name	Array Return Median value
James	5 (formula is here)
Robert	6

And looking up in the next sheet.... :

B_Sheet!

Name	Test Scores
James	3
Robert	4
James	5
Robert	6
James	7
Robert	8

It would be helpful if you could show us a sample of the data you have and desired results. Especially a link to a sample sheet tends to go a long way. — Kevin P., Jan 18 '23 at 16:08

Kevin P. · Answer 1 · 2023-01-18T17:05:31.913

There are a few ways you could do this. To answer your question using a formula that calculates the median based on the adjacent cells names you could use:

=IFERROR(BYROW(A2:A, LAMBDA(xx,MEDIAN(FILTER(B_Sheet!B:B, B_Sheet!A:A=xx)))),"")

However I would suggest the following formula which just groups and averages all names in B_Sheet.

=QUERY(B_Sheet!A:B,"SELECT A,AVG(B) WHERE A IS NOT NULL GROUP BY A LABEL AVG(B) ''")

The second formula can be pasted in cell A1 and it will populate Column A with a name and Column B with the average for that name. You can further sort this and filter it down as desired if you do not want every single name. Of course this calculates AVERAGE and not MEDIAN.

Return median value array in google sheets

1 Answers1