# getting data from several columns in a record

I have excel sheet contains the following table:

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

-
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

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.

-
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.

-