This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi all , many thanks for any help with this .
Im sure a relatively simple solution here I cannot find order for result in the formula .
Column "B" - a list of Dates
Column "D" - a list of Values / names
Xlookup formula to return date of 1st instance of specified Value "FOX". where "FOX" is value of cell F6.
=XLOOKUP($F$6,($D$9:$D$45),B9:B45,"----",0,1)
additionally another cell to return last instance of Value .
=XLOOKUP($F$6,($D$9:$D$45),B9:B45,"----",0,-1)
Any advice - to write format so that return array used / ie column "B" and return DATE is defined as only that greater than TODAY , and only dates of 1st / last instance after TODAY returned as result .
DD
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi,
If the dates are sorted in ascending order, then these formulas will work
=XLOOKUP(1,(($B$2:$B$17="Fox")*($A$2:$A$17>TODAY())),$A$2:$A$17)
=XLOOKUP(1,(($B$2:$B$17="Fox")*($A$2:$A$17>TODAY())),$A$2:$A$17,,,-1)
Hope this helps.
You are welcome. If my previous reply helped, please mark it as Answer.
Works perfectly - thank you so much / and for quick replies
=IFERROR(MINIFS($B$9:$B$45, $B$9:$B$45, ">"&TODAY(), $D$9:$D$45, F6), "")
and
=IFERROR(MAXIFS($B$9:$B$45, $B$9:$B$45, ">"&TODAY(), $D$9:$D$45, F6), "")