H Lookup error

[frustrated]

Hi All

I am trying to use the following Hlookup however it is returning an error =HLOOKUP("=(DAY(TODAY())-1)",$D$74:$AH$111,6,FALSE)

Please can someone help resolve this.

Thank you

[Norie]

Are you actually looking for the string '=(DAY(TODAY())-1)' or do you want to look for the value returned by DAY(TODAY())-1?

If it's the latter remove the "" and the second =.

Please Login or Register  to view this content.

[frustrated]

Hi Norie

This is excellent thank you.

Just to complicate it slightly, can I amend the above formula to return a value for the last weekday i.e. weekend values are omitted?

[Norie]

You would need a formula that would return the last weekday, and I'm afraid I don't have one.

However I'm pretty sure if you searched the forum you would find one.

[Vikas_Gautam]

Hi Frus...
Try this:-

Please Login or Register  to view this content.

Change your Weekend days in the formula in place of 6 and 7..
Untested Though...

[kvsrinivasamurthy]

For weekend saturday

Please Login or Register  to view this content.

For weekend sunday

Please Login or Register  to view this content.

[daddylonglegs]

So what is in the range D74:AH74? That's a 31 cell range, does it contain the numbers 1 to 31?What do you expect on 1st of the month when the previous workday is in the previous month?

If you use =DAY(TODAY())-1 then on 1st of the month that returns zero - do you have zero in the lookup range?

[frustrated]

Hi

Apologies for the delay in replying.

I have tried the formulas suggested but am not getting the desired result. Basically I would like the lookup to ignore the weekend completly.

For example the look up value according to Norie's formula is Today (Monday 22nd) - 1 = lookup value of 21 however I would like to return a lookup value for the last weekday which is Friday i.e the 19th

Thanks so far for your suggestions

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[frustrated]

Hi kvsrinivasamurthy

Thanks for your suggestion but I'm afraid that also did not work.

I have attached a copy of the sheet I am using and entered your suggested H lookup formula in column C.

To clarify:

If today is a Monday, I would like the lookup to put in the previous Friday's data into column C
If today is a Tuesday, I would like the lookup to put in the previous day's data (Monday) into column C
If today is a Wednesday, I would like the lookup to put in the previous day's data (Tuesday) into column C
If today is a Thursday, I would like the lookup to put in the previous day's data (Wednesday) into column C
If today is a Friday, I would like the lookup to put in the previous day's data (Thursday) into column C
If today is a Monday, I would like the lookup to put in the previous Friday's data into column C
Etc Etc

I am therefore looking to skip Saturdays and Sundays as data will only exist for a weekday


Thank you for your continued efforts. I am sure someone can crack this for me!

[daddylonglegs]

You can use this formula in C4 copied down to get data for the previous workday

=INDEX(D4:AH4,MATCH(DAY(WORKDAY(TODAY(),-1)),D$3:AH$3,0))

Obviously today that will give you all zeroes because you don't have anything in the 22 column

You didn't answer my question about previous months so I don't know whether that will work OK on 1st October......

[kvsrinivasamurthy]

Formula

Please Login or Register  to view this content.

[Punnam]

hI, TRY THIS

=HLOOKUP(IF(TEXT(TODAY(),"ddd")="Sun",DAY(TODAY())-2,DAY(TODAY()-1)),$D$3:$AH$17,2,FALSE)

PUNNAM

[boopathiraja]

May be try this

=INDEX($D$1:$AH$17,ROW(),MATCH(DAY(WORKDAY(TODAY(),-1)),$D$3:$AH$3,0)

[boopathiraja]

May be try this

=INDEX($D$1:$AH$17,ROW(),MATCH(DAY(WORKDAY(TODAY(),-1)),$D$3:$AH$3,0)

[kvsrinivasamurthy]

Pl see attached file.

[kvsrinivasamurthy]

Pl see attached file.

[kvsrinivasamurthy]

For C4

Please Login or Register  to view this content.

[frustrated]

Hi All

Thanks for the suggestions, the formulas provided all return the results required.

In response to daddylonglegs' question - I do not require data for the previous day once a new month begins.

Thanks everybody for your help. This can now be closed.