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

P

Pupishi

New Member
Joined
Oct 27, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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!!
 

Attachments

  • Ex1.png
    Ex1.png
    62.7 KB · Views: 3
  • Ex2.png
    Ex2.png
    66.2 KB · Views: 3
Last edited by a moderator:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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
Excel Formula: 
=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))
 
Upvote 0
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?

Fluff said:
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
Excel Formula: 
=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))
Click to expand...
 
Upvote 0
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.
 
Upvote 0
No idea if this works in Sheets, but try
Excel Formula: 
=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))
 
Upvote 0
Solution
Thank you very much!! The below formula worked in Google Sheets. And noted on your advice. Thank you again for your kind support!
Fluff said:
No idea if this works in Sheets, but try
Excel Formula: 
=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
Click to expand...
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Google sheets (sumifs function help needed)
Replies
3
Views
699
ExceLoki
ExceLoki
JohnGow383
  • Solved
Google Script - How to Copy Values from 1 sheet to another sheet into last row of specific columns
Replies
2
Views
6K
JohnGow383
JohnGow383
A
  • Question
How to run sequential functions in a single cell in excel
Replies
0
Views
560
AusFastLife
A
MrDB4Excel
  • Question
XLOOKUP Issue
Replies
8
Views
542
MrDB4Excel
MrDB4Excel
MrDB4Excel
  • Solved
Get Last Matching Date
2
Replies
13
Views
633
MrDB4Excel
MrDB4Excel

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top