SOLVED

Looking up values and skipping blank cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2094659%22%20slang%3D%22en-US%22%3ELooking%20up%20values%20and%20skipping%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2094659%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20set%20of%20customer%20data%20where%20each%20customer%20occurs%20in%20multiple%20rows%20(one%20row%20for%20each%20product%20that%20they%20have%20bought).%20In%20most%20cases%2C%20only%20one%20of%20the%20product%20rows%20has%20the%20customer%20ID.%20I%20would%20like%20to%20create%20a%20column%20where%20I%20can%20look%20up%20the%20customer%20name%20and%20if%20the%20customer%20has%20been%20assigned%20a%20customer%20ID%20in%20any%20of%20the%20product%20rows%2C%20I%20want%20to%20return%20that%20number%2Fname%20(cell%20value).%20Is%20there%20a%20dynamic%20formula%20that%20enables%20me%20to%20do%20this%3F%20See%20the%20example%20below.%20The%20output%20I%20want%20to%20get%20to%20is%20in%20column%20E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20only%20the%20last%20entry%20for%20customer%203%20has%20a%20customer%20ID.%20I%20would%20like%20all%20rows%20for%20customer%203%20to%20return%20the%20customer%20ID.%20In%20other%20words%2C%20I%20want%20to%20skip%20the%20cells%20in%20column%20D%20that%20are%20blank%20for%20this%20customer%20and%20return%20the%20cell%20that%20shows%20the%20ID.%20I%20have%20a%20set%20of%20200%2C000%2B%20customers%20so%20I%20need%20something%20dynamic%20and%20won't%20be%20able%20to%20change%20the%20formula%20for%20every%20customer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249176i65F4DB144F716896%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Excel.JPG%22%20alt%3D%22Excel.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2094659%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2094727%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20up%20values%20and%20skipping%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2094727%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F941993%22%20target%3D%22_blank%22%3E%40CWI_AK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%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%20524px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249186iC7EA6B06426B5244%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%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%5BCustomer%5D%2CXMATCH(1%2C(%5BCustomer%5D%3D%5B%40Customer%5D)*(%5BID%5D%26lt%3B%26gt%3B%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2094841%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20up%20values%20and%20skipping%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2094841%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%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20but%20I'm%20afraid%20this%20only%20returns%20the%20customer%20name%20and%20not%20the%20ID.%20Essentially%2C%20your%20solution%20provides%20a%20copy%20of%20the%20A%20column%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

Excel.JPG

 

 

Thanks!

7 Replies

@CWI_AK 

That could be

image.png

=INDEX([Customer],XMATCH(1,([Customer]=[@Customer])*([ID]<>"")))

@Sergei Baklan 

 

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

Best Response confirmed by CWI_AK (New Contributor)
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 

@CWI_AK 

Why didn't you provide the sample data as a workbook?

Based on @Sergei Baklan 's work

= XLOOKUP(@[Customer], IF([ID]<>"", [Customer]), [ID] )

@Peter Bartholomew , thank you for the correction.

More legacy way

= LOOKUP(2,1/(@[Customer]=[Customer])/([ID]<>""), [ID] )

@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