Forum Discussion
Solved
Question XLOOKUP finding the next match row if blank
Hi, Question: I have a data set which using XLOOKUP returns an email address for a customer number. ON the email data set, there are multiple emails for each customer number. How can I retur...
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.