# HLOOKUP??

#### msimone

##### New Member
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?

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?

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.

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?

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.

Replies
0
Views
246
Replies
2
Views
329
Replies
15
Views
1K
L
Replies
8
Views
714
Legacy 352679
L
Replies
7
Views
263

1,219,960
Messages
6,151,164
Members
451,012
Latest member
OH650R

### 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.

### Which adblocker are you using?

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

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