HLOOKUP Formula not selecting correct date

[Aaronb4]

I am currently trying to set up a personnel resourcing spreadsheet with a "SUMMARY" page that will display a total number of persons in each position based on todays date.

the summary page has a cell with =TODAY() that works fine,
I also have the following formula running to pull the data off a seperate table on another sheet : =IF(HLOOKUP(TODAY(),Sheet!184:185,1,TRUE)=TODAY(),HLOOKUP(TODAY(),Sheet!185:200,16,TRUE),NA())

for some reason it is only selecting the first date in the date and not cross referencing the dates.

i have added some screenshots for reference,

 

[6StringJazzer]

it is only selecting the first date in the date and not cross referencing the dates.

I do not understand this statement. "first date in the date" is not clear and I don't know what you mean by "cross referencing".

The formula is doing what it says it does. It finds an approximate match for TODAY in row 184 (it is approximate because you are using TRUE as the last argument to HLOOKUP). It finds that date in column H (assuming your screenshot is from the say day you posted this question). Then it pulls the result from the same row (argument 1), and it is TODAY. Then you proceed to get the corresponding data from row 200. With this data the result is correctly shown as 0.

What are you expecting instead, and why?

This is not a great way to do this, by the way. There is no need to first check to see if the date exists. Also I am not sure why you are looking for an approximate date. The simplified formula below should also do what you want, returning NA if an exact match is not found:

=IFERROR(HLOOKUP(TODAY(),Sheet!185:200,16,FALSE),NA())

 

[Aaronb4]

I do not understand this statement. "first date in the date" is not clear and I don't know what you mean by "cross referencing".

The formula is doing what it says it does. It finds an approximate match for TODAY in row 184 (it is approximate because you are using TRUE as the last argument to HLOOKUP). It finds that date in column H (assuming your screenshot is from the say day you posted this question). Then it pulls the result from the same row (argument 1), and it is TODAY. Then you proceed to get the corresponding data from row 200. With this data the result is correctly shown as 0.

What are you expecting instead, and why?

This is not a great way to do this, by the way. There is no need to first check to see if the date exists. Also I am not sure why you are looking for an approximate date. The simplified formula below should also do what you want, returning NA if an exact match is not found:

=IFERROR(HLOOKUP(TODAY(),Sheet!185:200,16,FALSE),NA())

Hi sorry i was not very clear in my explanation.

So on the summary page it has Todays Date (Tuesday 3 January 2023), this date changes as the date does with =TODAY(). as that date changes i would like the numbers in the below tables on that page to reflect the corresponding numbers of the same date in the second sheet.

i will attempt to do your formula, i apologise again i am extremely new to this. thanks for your assistance

 

[Alex Blakenburg]

Did you try Jeff's formula ? I think he might be a row out and his range should start at 184 and not 185, you will need to check if that means your 16 should be 17.

        =IFERROR(HLOOKUP(TODAY(),Sheet!184:200,16,FALSE),NA())

Assuming that formula goes in N19 down, you might want to throw in a Match for the row lookup eg.

=HLOOKUP( TODAY(), Sheet1!$184:$200, MATCH(M19,Sheet1!$B$184:$B$200,0), FALSE)

(Put in N19 and copy down to N26)

 

[6StringJazzer]

I think he might be a row out

Yes, you are correct. I carried over an error in the original formula.