XLOOKUP will not allow to lookup the entire column. For Google Sheets.

[Pupishi]

Dear Excel Experts,

I have a below formula whereby it returns the name with the closest date from today's date.

=LET(f,FILTER(I2:I18,E2:E18=R2),XLOOKUP(TODAY(),P2:P18,f,XLOOKUP(TODAY(),P2:P18,f,,-1),1))

While the formula works fine when the range is set with specific rows, when I set the range to be the entire column, the value is returned as '#VALUE!'.
I have tried to put 9999 as the limit, but this also results in the same error.

Would like to see if there is any way I could look up the entire column without having to specify the cell rows.
Any kind of help/advice would be much appreciated!!

 

[Fluff]

What version of Excel are you using, as 2016 doesn't have Filter or Xlookup?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=LET(f,FILTER(I2:P10000,E2:E10000=R2),XLOOKUP(TODAY(),TAKE(f,,-1),TAKE(f,,1),XLOOKUP(TODAY(),TAKE(f,,-1),TAKE(f,,1),,-1),1))

 

[Pupishi]

Thank you so much for the advice! Confirm the below works in Excel. However, it seems not workable, as there is no TAKE function in Google Sheets. May I know if there would be any alternate function that is workable in Google Sheets?

What version of Excel are you using, as 2016 doesn't have Filter or Xlookup?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=LET(f,FILTER(I2:P10000,E2:E10000=R2),XLOOKUP(TODAY(),TAKE(f,,-1),TAKE(f,,1),XLOOKUP(TODAY(),TAKE(f,,-1),TAKE(f,,1),,-1),1))

 

[Fluff]

If you want a formula for Google sheets you need to make that clear right from the start. Also all such questions should be posted in the General Discussion & Other Applications section of the board.
I have done that for you this time.

 

[Fluff]

No idea if this works in Sheets, but try

=LET(f,FILTER(I2:P10000,E2:E10000=R2),XLOOKUP(TODAY(),INDEX(f,,8),INDEX(f,,1),XLOOKUP(TODAY(),INDEX(f,,8),INDEX(f,,1),,-1),1))

 

[Pupishi]

Thank you very much!! The below formula worked in Google Sheets. And noted on your advice. Thank you again for your kind support!

No idea if this works in Sheets, but try

=LET(f,FILTER(I2:P10000,E2:E10000=R2),XLOOKUP(TODAY(),INDEX(f,,8),INDEX(f,,1),XLOOKUP(TODAY(),INDEX(f,,8),INDEX(f,,1),,-1),1))

han

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Alex Blakenburg]

I'm a bit curious, what is having the 2 Xlookups actually doing for you. Does this not give the same result ?

=LET(f,FILTER($I$2:$P$10000,$E$2:$E$10000=R2),XLOOKUP(TODAY(),INDEX(f,,8),INDEX(f,,1),"",1,1))

PS: I prefer to have the Today() in a cell it makes it much more flexible and much easier to test with different values.