Jan 25 2021 09:30 AM
Hi,
I have a set of customer data where each customer occurs in multiple rows (one row for each product that they have bought). In most cases, only one of the product rows has the customer ID. I would like to create a column where I can look up the customer name and if the customer has been assigned a customer ID in any of the product rows, I want to return that number/name (cell value). Is there a dynamic formula that enables me to do this? See the example below. The output I want to get to is in column E.
For example, only the last entry for customer 3 has a customer ID. I would like all rows for customer 3 to return the customer ID. In other words, I want to skip the cells in column D that are blank for this customer and return the cell that shows the ID. I have a set of 200,000+ customers so I need something dynamic and won't be able to change the formula for every customer
Thanks!
Jan 25 2021 09:39 AM
Jan 25 2021 09:58 AM
Thanks, but I'm afraid this only returns the customer name and not the ID. Essentially, your solution provides a copy of the A column
Jan 25 2021 10:10 AM
Solution@CWI_AK
As you like Customer Id to return =INDEX([ID],XMATCH(1,([Customer]=[@Customer])*([ID]<>""))) this would be fine. To make it dynamic use table
Jan 25 2021 10:14 AM
Why didn't you provide the sample data as a workbook?
Based on @Sergei Baklan 's work
= XLOOKUP(@[Customer], IF([ID]<>"", [Customer]), [ID] )
Jan 25 2021 11:13 AM
@Peter Bartholomew , thank you for the correction.
More legacy way
= LOOKUP(2,1/(@[Customer]=[Customer])/([ID]<>""), [ID] )
Jan 25 2021 11:33 AM
@bovasclion Thanks. This works fine!
@Sergei Baklan Thanks again, should have been intuitive for me to switch out customer and id in the formula but couldn't get it to work on my own
Jan 25 2021 10:10 AM
Solution@CWI_AK
As you like Customer Id to return =INDEX([ID],XMATCH(1,([Customer]=[@Customer])*([ID]<>""))) this would be fine. To make it dynamic use table