HLOOKUP??

msimone

New Member
Joined
Sep 30, 2006
Messages
17
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

PGuru

Board Regular
Joined
Sep 16, 2006
Messages
55
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

New Member
Joined
Sep 30, 2006
Messages
17
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

Board Regular
Joined
Sep 16, 2006
Messages
55
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

New Member
Joined
Sep 30, 2006
Messages
17
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

Board Regular
Joined
Sep 16, 2006
Messages
55
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.
 

Forum statistics

Threads
1,136,271
Messages
5,674,746
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top