Lookup function - Excel

[DubTrub]

In the spreadsheet below, I am wishing to obtain a result in the red box, in which it looks in that row for the location of the latest entry, and returns the date above (in this case, it should find "D/F" in column F and return the date 03/10/04.

I should then be able to drag the formula down for each row, so the result for row 6 would be 02/04/05.

Any Pprooners out there who can help?

[Duckbutt]

Pls check your e-mails DT

[DubTrub]

Thanks very much to Duckbutt for resolving my spreadsheet issues. Pprooners are the best!

[Cyclic Hotline]

So what was the solution?

Haven't seen you hanging around here beyond the black stump! Used to be a quiet place!

[Maxflyer]

Yes, please share the answer as it would help me immensely with a current problem.

Thanks

MF

[Duckbutt]

Difficult to post it on here but for anyone whose interested, send me your e-mail URL in a PM and I will send you a copy of my idea of a solution as an attachment.

DB

[JollyNomad]

Greetings

If anyone still cares, below is a way of achieving this with a formula. I am sure it can be improved/shortened

Notes:

This is an array formula so when after is has been typed or pasted into the formula bar you must press CTRL+SHIFT+ENTER and not just Enter! If you do it wont work. You'll notice after you have entered it that it is now enclosed by braces - {} basically, this allows for loops. Unfurtuately you can't just type them.
It is designed to work with DTs sheet fo should be placed in K3. After that just drag it down.

=IF((MAX((B3:J3<>"")*COLUMN(B3:J3)))-COLUMN(B3:J3)+1 > 1,INDIRECT(ADDRESS(MAX((B3:J3<>"")
*ROW(B$2:J$2)),COLUMN(B3:J3)+(MAX((B3:J3<>"")*COLUMN(B3:J3)) )-COLUMN(B3:J3),4)),"")

Apologies if I am just repeating your answer DB

Hth

Cheers

[Duckbutt]

No duplication JN, my way involves creating a couple of 'shadow' tables (for want of a better description) and using far more simple formulae with an HLOOKUP.

At a first glance I am not ashamed to admit that your solution is somewhat beyond the 'cutting edge' of my knowledge and would seem more elegant once set up correctly.

DB

[ExGrunt]

A slightly shorter solution is:

=IF(COUNTBLANK(B3:J3)<>9,INDEX($B$2:$J$2,1,MAX(ISTEXT(B3:J3) *{1,2,3,4,5,6,7,8,9})),"")

As above, this is an array formula so copy it to cell K3, then press Ctrl-Shift-Enter.

EG

[JollyNomad]

Like it but what happens if an extra column needs to be inserted?

JN

[ExGrunt]

Extra columns

The easiest way to add additional columns would be to insert a new row, say row 3. In this row you would enter a value corresponding to the column - eg cell B3 value 1, cell C3 formula =B3+1, cell D3 formula =C3+1 and so on to the last column.

Then the formula, which would now be first inserted in cell K4, would be:

=IF(COUNTBLANK(B4:J4)<> 9,INDEX($B$2:$J$2,1,MAX(ISTEXT(B4:J4)*($B$3:$J$3)),"")

You then have to update the formula to change the column identifier from J to the last column letter in your new sheet and the value 9 to the value in the last cell in new row 3. (Note: this is a good example of why it is considered poor practice to use constants in formulae ).

A degree of automation can be achieved by, erring from the KISS principle , using named ranges. Define the following names:

Dates $B$2:$J$2
ColCount $B$3:$J$3 (Using a new row as above)
LastCell $J$3

The formula would then be:

=IF(COUNTBLANK(B4:J4)<>LastCell,INDEX(Dates,1,MAX(ISTEXT(B4: J4)*(ColCount))),"")

Then after inserting columns the formula 'should' update automatically once rows 2 & 3 have data.

Hope this helps

EG

[DubTrub]

EG's solution is certainly simple and elegant, and it works well in my application.
In order add columns past the last date, I added an additional blank column (after Column J) and used this as the identifyer for the ranges, thus:

Dates $B$2:$K$2
ColCount $B$3:$K$3
LastCell $K$3

I just have to remember to drag the formula in Row 3 across to the new column.

(My red box now looks for the last date, and if older than 3 months, highlights red.)

Thanks for everyone's tremendous assistance.

[ExGrunt]

Glad to be of help.

As a final point in EG's 'good excel for aviators' course, if you are using this spreadsheet for work, it is good practice to build in error checking calculations. In my example, I suggest at least:

=COLUMNS(Dates)=LastCell

If all is well this should evaluate to TRUE, if not then it will be FALSE. This will give you a visual indication if you have not copied the formula across correctly.