HLOOKUP??

[msimone]

I've built a calendar in excel to track transactions per day. At the top of each calendar square is the date (1 - 31 obviously) and underneath I list transactions (+/-). At the bottom of each calendar square is the sum of those transactions, pitifully simple I know. I'd like to have a cell that displays the subtotal for the current day when I open the spreadsheet. I've been using this:

=HLOOKUP(J88,A75:U83,9,FALSE)

IN J88 I've put =DAY(TODAY()) and A75:U83 are the coordinates of my "calendar square" and "9" equals the number of rows down where my subtotal box is. This solution works fine if row 75 is the top row but if I wanted to expand it it doesn't work. I would imagine there's a simpiler way to have a formula search a worksheet for a specific number (date) which is today's date / number and then return the information from a certain number of rows down. I hope this makes sense! Please help!

-msimone

 

[PGuru]

What do you mean "expand"?

If there are to be more rows then U83 needs to become U84, and the 9 needs to become 10.

But you'd know that, so what answer are you getting?

 

[msimone]

Basically it seems that if the date I'm doing the search for (in this example let's just say "30" for Sept 30th, in order for the formula I posted below to work the "30" needs to reside within the top row of the HLOOKUP. If I alter the formula to include one row above where "30" resides then I get the #N/A error. So is there a way for HLOOKUP to perform it's search within ALL the cells inside of a range instead of just those in that row and that column? Does that make sense?

 

[PGuru]

Yes that makes sense, but HLOOKUP doesn't work like that. It only looks on one row (the first of those specified), in the same way that VLOOKUP only looks at one column.

One possible solution could be to move the days 1-31 to row 1, put your subtotal in row 2 and run the lookup on those two rows. You can always hide them and keep all your data visible in the same spot they are now.

 

[msimone]

That's true, thanks for the advice. But is there no excel formula (or comination of formulas) that can search an entire worksheet for a value and then return a related value from a specified position based on the found value?

 

[PGuru]

I think there may well be, but you run the risk of it failing if you leave it too general.

For example, what if you sell 29 wotsits one day? A general search may find that on the 29th instead of whatever value is on the 29th.

As a mainly self-taught Exceler, I cover the gaps in my knowledge by adapting my requirements, as you'll have seen. Hopefully someone will be along soon to render me superfluous, but at least your thread gets bumped.