Excel Diary Project Code Lookup

Hi,

You will need to unmerge your cells to allow this array formula** to be entered in B2:

=INDEX($B$23:$L$23,MIN(IF($B$24:$L$56=$A2,COLUMN($B$24:$L$56)-MIN(COLUMN($B$24:$L$56))+1)))+INDEX($A$24:$A$56,MIN(IF($B$24:$L$56=$A2,ROW($B$24:$L$56)-MIN(ROW($B$24:$L$56))+1)))

Copy down as required.

Change the two occurrences of MIN(IF to MAX(IF and leave everything else the same for the formula in D2.

Note that this formula will return a "midnight" time as the next day, e.g. for DFD41723 the last date will be given as 05/07/2014 00:00 and not 04/07/2014 00:00 as it technically appears in your sheet. It's arguable which is "correct", though let me know if you'd prefer it to be the latter.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You're welcome!

Forgive me, but I'd have to ask you first to clarify what you mean by "explain".

I've had many similar requests in the past, only to find that, when I've given a detailed breakdown of the formulas and logic involved, the general reply has been along the lines of "Oh! Er thanks!" (i.e. "I never realised it would be so complicated!").

I don't mean to condescend. It's simply that I'd rather not go into a detailed deconstruction if it's not really going to be of much use to you. That's why I ask what you expect in terms of an "explanation".

Do you mean just the general concepts/logic behind the formula (i.e. a couple of lines)? Or perhaps a full deconstruction, including detailed explanations for each of the functions involved (this could take a whole page on its own in this forum)?

What level of Excel do you currently have? Which parts of the formula do you already understand? Which don't you? What methods have you used so far in an attempt to deconstruct the formula?

Regards

Hi,

This part:

MIN(IF($B$24:$L$56=$A2,COLUMN($B$24:$L$56)-MIN(COLUMN($B$24:$L$56))+1))

simply checks which of the entries in B24:L56 are equal to the value in A2 and, if so, returns their relative column position. For all of these, we then take the minimum (or maximum, for the other formula) and pass this column to the INDEX.

The other part is precisely the same but that it returns a relative row number.

Regards

You're welcome.