Forum Discussion
Question XLOOKUP finding the next match row if blank
Hi Sergei, I'm trying to use the Xlookup method so that I can reverse the search order. It works in Excel, but not Google Sheets. I'm guessing it has to do with the way arrays are handled. Any thoughts?
I'm not familiar with Google Sheets, but it looks like last-to-first works in it
SergeiBaklan amazing, i used the second with the XLOOKUP and it worked.... i tried a ton of others including filters and what not. this worked!
leanneypants glad it helped, thank you for the feedback
Thanks Sergei this has worked great for me also!
On the xlookup what does the &1 mean also &-- In the formula?
Cheers
Rich
Rich, glad to help.
Here (C2:C7<>"") returns an array with TRUE or FALSE. Double dash converts them to 1 or 0 accordingly, thus --(C2:C7<>"") returns an array with 1 for non-empty cells and 0 for empty ones.
By & we concatenate B2:B7 with this array (for example ="a" & "b" returns "ab"). Resulting array will be {"f11", "f2", "f21", "f3",...}.
=D3&1 returns text "f21". Finally we try to find this text in above array and return related value from C2:C7.
SergeiBaklan thank you so much. I have learned something new with this, brilliant!
Each of us learns something new on this resource, I'm not an exception. Anyway, glad to know it helped you, thanks for sharing.
- that's brilliant - thank you!
aussieC , you are welcome
