Median Indirect: Find median in range and bring back adjacent cells

All,

I would like to find the median in a range of cells and then bring back the 2 adjacent cells

Range...............Adj Cell 1............Adj Cell 2
1.......................L1......................L2
2.......................P3......................P4
3.......................O9......................010

So the median of the range is 2, thus we would want the formulas to bring back P3 and p4

Column 1..............Column 2
P3...........................P4

Thanks all you really are such a super smart bunch

Keelin

Yes I was just thinking through that, not sure if its possible.......

Do you think it is it possible to find the closest to the median in the range?

I think I will use a helper column and use a true statement to find the closest one......possibly!

Oh Lordy thats totally awesome

I only hope that I can do this justice and get it working in the master spreadsheet

......I'll try it now and report back

THANK-YOU SOO MUCH

Oh its awesome....we are nearly there...

Although there is a complication, there is a condition on the median,

e.g. we are calculating the median for the a subdivision, e.g. Finance - Accounting; or Finance - End of Yr

So I need to build in a condition and I am failing miserably - hence I am back here to leverage the collective genius that is this forum

My current formula is:

=IF('DATA MERGE'!D:D=Y5,INDEX('DATA MERGE'!K:K,MATCH(MAX(IF('DATA MERGE'!J:J<=MEDIAN('DATA MERGE'!J:J),'DATA MERGE'!J:J)),'DATA MERGE'!J:J,0)))

Essentially I need to build in an if a condition to evaluate if a value column d of data merge meets the condition of cell Y5

Any help would be GREATLY appreciated

Thanks

Keelin

Thanks so much

Its bringing back a value but not the right one for me -----I have been staring at the screen for too long.

I have attached an example of what I am trying to do with the example sheets and model that I am using

Would you mind having a look to see if I am going mad?

Thanks so much,

Keelin

Ahh soo sorry I did in haste and forgot it had all of the formula's still in it!

Thanks so much for having a look, its about as small as I can get it now (still 100kb though is that ok?)

Thanks so very much,

K