Forum Discussion

aussieC's avatar
aussieC
Copper Contributor
Oct 12, 2020
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 return the next email address using XLOOKUP if there are blanks in this email set? 

 

Any clues?!

13 Replies

  • aussieC 

    A couple of thoughts.  An alternative to searching for concatenated conditions is to simply strike out the customers that do not have declared email from consideration.  If there isn't an entry with an email, you could always relent and use the 'if not found' parameter to repeat the search to return such data that may exist.

    = XLOOKUP( @selected, IF(email<>"", customer), email:phone )
    or
    = XLOOKUP( @selected, IF(email<>"", customer), email:phone,
      XLOOKUP( @selected, customer, email:phone ) )

    • KSolak's avatar
      KSolak
      Copper Contributor

      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?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        I'm not familiar with Google Sheets, but it looks like last-to-first works in it

    • leanneypants's avatar
      leanneypants
      Copper Contributor

      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!

    • R_Dunbar's avatar
      R_Dunbar
      Copper Contributor

      SergeiBaklan 

       

      Thanks Sergei this has worked great for me also!

       

      On the xlookup what does the &1 mean also &-- In the formula?

       

      Cheers

       

      Rich

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        R_Dunbar 

        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.

Resources