# Return value in range of cells using multiple criteria

#### onthegreen03

##### Board Regular
Hi All - I have a large data set which contains customer numbers and when they started or ended services with us. See the example below. In the top box is the actual data set, with multiple rows of data for the same customer. The bottom box is the result I need which summarizes each customer and combines when they started and ended throughout the time period. I toyed around with INDEX and MATCH to try to get the right result but it didn't work. Is there a formula/function I can use that will look for all results in a certain column and return that value based on customer number? Thank you!

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
How about in C19 dragged down & across
Excel Formula:
``=IFERROR(INDEX(C\$4:C\$15,AGGREGATE(15,6,(ROW(\$B\$4:\$B\$15)-ROW(\$B\$4)+1)/(\$B\$4:\$B\$15=\$B19)/(C\$4:C\$15<>""),1)),"")``

Hi

365 BETA
C19 dragged down
Excel Formula:
``=BYCOL(IF(B\$4:B\$15=B19,CONCATENATE(C\$4:V\$15),""),LAMBDA(array,CONCAT(array)))``

Thanks for the response Fluff, and as always you are amazing. Happy New Year!

You're welcome & thanks for the feedback.

Assuming no start and end on the same day, C19 copied across and down:

Excel Formula:
``=CONCAT(IF(\$B\$4:\$B\$14=\$B19,C\$4:C\$14&"",""))``

Replies
2
Views
75
Replies
2
Views
132
Replies
5
Views
85
Replies
6
Views
287
Replies
3
Views
283

1,203,683
Messages
6,056,730
Members
444,887
Latest member
cvcc_wt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back