Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have excel sheet contains the following table:

enter image description here

I want to get the last recent DATE with its LAB Result?

share|improve this question
    
Please be more specific. I am guessing you want 18% from 9-Apr-12 as a result, but it could also be the empty result of 18-Jun-12. What about 11-Sep-11? Are such voids common for your lab results? This is important, because it is a major difference, if you have countinuous data or data with voids. –  Jook Nov 6 '12 at 13:36

2 Answers 2

I would suggest a two part approach, i believe it should be possible though to merge that into a single formula. First create a helper column with this formula:

IF(A2=MAX(A$2:A$9),1,0)

It will turn 1 if the date in the row is the maximum date in the list (here A2 to A9) Now use a vlookup to get the LAB Result for the row with the 1. Done.

share|improve this answer
    
when applying the formula IF(A2=MAX(A2:A9),1,0) then drag it down for each cell in column it is fixed, so I need to change A2 & A9 for each cell manually ! and this is not practical?? –  Adban Nov 6 '12 at 13:06
    
ah yes. give me a second, i will change the formula so you can copy it down. If you prefix the part of the Cell name with a dollar sign, it will not increment that part when you copy it down. So while A2 will turn to A3 etc. A$2 wont. –  TheUser1024 Nov 6 '12 at 13:39

=VLOOKUP(MAX(A2:A7),A:D,4,FALSE) would be my suggestion

Col A has the Date - Col D has the lab result.

Whereby 18-Jun-12 is in A1, and excluded. However this solution won't check if there actually is a LAB-Result. Then again, it is not clear to me, if this is necessary.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.