Search code examples
excelrangevlookupmedian

Finding the median of a range of values selected using vlookup

Column A are dates and B & C are Measurements

Dates   Measurements        
1   56  15  
2   45  25  
3   62  76  
4   15  42  
5   165 56  
6   16  79  
7   45  46  
8   47  79  
9   24  47  
10  12  14  
11  147 47  
12  195 19  
13  443 79  
14  642 43  
15  462 75  
16  156 87  
17  794 49

Start Date:2
Measurement:45
Code used to solve for the measurement
=VLOOKUP(B21,A2:C18,2,FALSE)

end date:14
Measure:642

=VLOOKUP(B22,A2:C18,2,FALSE)

I used vlookup to find me the values that I desire, but now I want to find the median values of that range from the start to end date in each column.

How can I code it so that once it selects the values, it can select the whole range and find the median values?

Solution

  • Since your column A values are ordered ascendingly, we can use the very efficient:

    =MEDIAN(INDEX(B2:B18,MATCH(B21,A2:A18)):INDEX(B2:B18,MATCH(B22,A2:A18,0)))

    Regards