Sep 24 2022 02:25 AM
Hi
I have a database with various customers with their email addresses. I use the XLOOKUP to get the email addresses of a specific person. My problem is that the result bring back "0" value and not the email address when the same person have "blank" in the email address fields. Sample:
Source:
Dale Isaac | |
Dale Isaac | email address removed for privacy reasons |
Dale Isaac | |
Dale Isaac |
Formula used: =XLOOKUP(B22;contact[fullname];contact[emailaddress1]; " " )
Result:
"0" (Zero)
How can I fix it that the email is in the lookup?
Sep 24 2022 02:45 AM - edited Sep 24 2022 02:52 AM
@007Nic XLOOKUP returns only the first match it finds. So, it finds the first "Dale Isaac" and returns whatever it finds in the email column on that row. If that cell is blank the result is 0.
You need to look into FILTER and TEXTJOIN. Then you can get all email addresses for a particular person and join them into one text string. If that's what you want, of course.
EDIT: file with an example attached.
Sep 24 2022 03:28 AM
Sep 24 2022 03:56 AM
@007Nic Did you see the file I attached earlier? Remove the 2nd email address and the formula will still work. But, if you are 100% sure that there will never be more than one email address per person you could shorten the formula by removing the arguments relating to the TEXTJOIN function (bold red below).
=TEXTJOIN(", ",TRUE,FILTER(contacts[emailaddress],(contacts[fullname]=E7)*(contacts[emailaddress]<>""),""))
Sep 24 2022 07:35 AM - edited Sep 24 2022 07:56 AM
Hi Riny, thank you this works with the blank cells excluded and only return the email address. I now get the SPILL error since it is a table and want to return multiple same email addresses. This is due to multiple of the same email addresses where the person are linked to multiple accounts within the database. How can I resolve that I only get one email address.
Sep 24 2022 07:59 AM
@007Nic Then you need to introduce UNIQUE also. See attached.
Sep 24 2022 09:05 AM