Hi guys,
I’m trying to create a spreadsheet, similar to calendar . But dates are forever changing so I want to add a list of dates that I can alter so they automatically change on my calendar.
I want my calendar to add any dates that match start date
and add ‘column A’ eg Wade’s crutching.
if this at all possible or if I’m going the wrong way about this!
any help would be most appreciated!
iPhone 14, iOS 18
Hi K_jubb,
The XLOOKUP function is your friend.
The XLOOKUP function searches a range for a specified value and returns the value from the same row in another column.
XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)
More here:
I placed these two tables on the same sheet for easier construction of the formula, also for easier illustration in a screen shot.
Formula in Calendar B2 is XLOOKUP($A2,Table 1::B,Table 1::A,"")
Table 1 does not need to be in date order. XLOOKUP will find the target.
You can sort Table 1 by Start Date if you wish, and the formula will still work.
Regards,
Ian.
Hi K_jubb,
The XLOOKUP function is your friend.
The XLOOKUP function searches a range for a specified value and returns the value from the same row in another column.
XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)
More here:
I placed these two tables on the same sheet for easier construction of the formula, also for easier illustration in a screen shot.
Formula in Calendar B2 is XLOOKUP($A2,Table 1::B,Table 1::A,"")
Table 1 does not need to be in date order. XLOOKUP will find the target.
You can sort Table 1 by Start Date if you wish, and the formula will still work.
Regards,
Ian.
Numbers: look up date then add text
