Hi Excel Forum,
I'm needing your help again on a issue i'm currently having trying to return a text field if a date equals if the Lookup Date equals the date in the table and the Return Field Day equals the the same day that the Date falls under.
So the example in the sample file i'm using 23/2/2022 which should return Wednesday just returns #VALUE!
the formula i'm using isThanks
Please Login or Register to view this content.
DHarvey
The syntax is wrong and you can't use a 2D array with XLOOKUP.
It would help if you told us what you were trying to do.
If this is meant to be doing what I think it is, then this should work:
=LET(d,C2,SEQUENCE(1,7,d-WEEKDAY(d,11)+1))
Again, no need for the lookup table.
Last edited by AliGW; 05-08-2022 at 01:19 AM. Reason: Workbook attached.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGW,
My apologies that's not what i was looking for i might not have explained it clear enough and that's my mistake. I have tweak the spreadsheet a little to make it a little clearer.
What I want is a formula that will look at the date in C2 and search the table of dates from C8:I12 and if its in the Wednesday column return with Wednesday in cell C4.
Hope this is a little clearer now
Thanks
DHarvey
Ah, I see!
Try this:
=INDEX(Table1[#Headers],SUMPRODUCT((Table1=C2)*(COLUMN(Table1[#Headers])-2)))
Hi AliGW,
Thanks that work as i wanted it to, apologies for not being clearer first time.
Thanks
DHarvey
No worries. It always helps to add expected outcomes to sample workbooks! A non-functioning formula that is already incorrectly built is difficult to reverese engineer for intent.
Why is there a need to put up a table and lookup to this table for day of week of a particular date when you can get the day of week from the date field itself?
C3=C2 format cell as "dddd" or C3=TEXT(C2,"dddd")
That's a very good question, Joseph!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks