Excel Help!

Greg · 14 Oct 2011 at 09:22

Ok,

I've got a spreadsheet that I'm trying to create and one of the requirements is as follows; I have a load of tasks down the left side, with a row of dates along (they are split into monthly sheets) the top. What I'd like on the right hand side is an automatically filled column on the right hand side of the sheet that reads the row for a last filled date for each specific task, that then reads the date that it was completed and enters it on the right hand side. I think its called HLOOKUP but i've no idea how to use it?

Example:

Uploaded with ImageShack.us

So where the Black box is, those dates are automatically filled by some clever code that reads across each of their respective columns and reads the date along the top row!

Thanks

Participant · 14 Oct 2011 at 09:45

You won't be able to use HLOOKUP as that only looks left to right. You could use INDEX & MATCH.

Edit: seems that normal LOOKUP can also do this:

Greg · 14 Oct 2011 at 11:04

Thats exactly what I'm after, but I can't understand the formula, could you quickly run through them both for me?

Sorry to be a nuicence!!!

Thanks in advance!

Greg

Participant · 14 Oct 2011 at 13:02

INDEX returns a value from the specified row and column of a range. I.e. it looks at cells A1:G4, and returns the value from row x and column y. In this case, the row is 1 (the dates) and the column is defined by a nested MATCH formula. The MATCH formula finds the number '1' on the row in question, and this completes the INDEX formula. The final '1' in the formula is the type of MATCH you wish to make...

-1 finds the smallest value that is greater than or equal to '1'.
0 finds the exact value.
1 finds the largest value that is less than or equal.

I usually put 1 out of habit but in this case I don't think it matters.

LOOKUP is just like VLOOKUP and HLOOKUP but can only return data from a single column at a time. It's also more flexible in some ways. '1' is the value you're looking for, B2:G2 is where you're looking for it, B1:G1 is the corresponding row you want the value to be returned from. The '$' relate to relative and absolute cell references, basically affecting how the formulae behave when you copy them to other cells.

Beyond this, the best way to understand it is to type it out yourself and look at the Excel range handles and comments.