Forum Discussion
Looking up values and skipping blank cells
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!
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
7 Replies
Why didn't you provide the sample data as a workbook?
Based on SergeiBaklan 's work
= XLOOKUP(@[Customer], IF([ID]<>"", [Customer]), [ID] )PeterBartholomew1 , thank you for the correction.
More legacy way
= LOOKUP(2,1/(@[Customer]=[Customer])/([ID]<>""), [ID] )
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 tablebovasclion Thanks. This works fine!
SergeiBaklan 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
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
