SOLVED

Question XLOOKUP finding the next match row if blank

%3CLINGO-SUB%20id%3D%22lingo-sub-1769863%22%20slang%3D%22en-US%22%3EQuestion%20XLOOKUP%20finding%20the%20next%20match%20row%20if%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1769863%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Question%3A%3C%2FP%3E%3CP%3EI%20have%20a%20data%20set%20which%20using%20XLOOKUP%20returns%20an%20email%20address%20for%20a%20customer%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EON%20the%20email%20data%20set%2C%20there%20are%20multiple%20emails%20for%20each%20customer%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20return%20the%20next%20email%20address%20using%20XLOOKUP%20if%20there%20are%20blanks%20in%20this%20email%20set%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20clues%3F!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1769863%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1772034%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20XLOOKUP%20finding%20the%20next%20match%20row%20if%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1772034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F828435%22%20target%3D%22_blank%22%3E%40aussieC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20608px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226054i86AD574F87BB6C65%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1773050%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20XLOOKUP%20finding%20the%20next%20match%20row%20if%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1773050%22%20slang%3D%22en-US%22%3Ethat's%20brilliant%20-%20thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1774230%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20XLOOKUP%20finding%20the%20next%20match%20row%20if%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774230%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F828435%22%20target%3D%22_blank%22%3E%40aussieC%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1863086%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20XLOOKUP%20finding%20the%20next%20match%20row%20if%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1863086%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you%20so%20much.%26nbsp%3B%20I%20have%20learned%20something%20new%20with%20this%2C%20brilliant!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?!

9 Replies
best response confirmed by aussieC (New Contributor)
Solution

@aussieC 

That could be like this

image.png

that's brilliant - thank you!

@aussieC , you are welcome

@Sergei Baklan thank you so much.  I have learned something new with this, brilliant! 

@jjaniga 

Each of us learns something new on this resource, I'm not an exception. Anyway, glad to know it helped you, thanks for sharing.

@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 ) )

image.png

Thanks @Peter Bartholomew - very helpful! Cheers

@Sergei Baklan 

 

Thanks Sergei this has worked great for me also!

 

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

 

Cheers

 

Rich

@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.