Lookup and Return Multiple Matches

I have been working with a worksheet that has a lot of information on it. I am trying to have a look-up formula to return a list of all results that have an answer.

My workbook is for figuring out the loads in a electrical panel. I have condition formatting and data verification. I am now trying to get a list of what breakers are being used in the panel and how many of each. I am using countif() to give the results per breaker size. Below that, I have a concatenate() to show the total of breakers and what type. Now here is where I am drawing a blank...I want to then return a list of every breaker that is being used, and then list how many of each.

The breaker info is input into cells AD8:AD49 & AV8:AV49. (These cells are set up as a pull down list {data validation}, with conditional formatting if an error is detected in the input)
The count if function is set up to read AD8:AD49, and count if they match the cell in BQ117, and so on down the horizontal list. (row 118 is for AD8:AD49 & row 119 is for AV8:AV49)
Row 124 sums each breaker, and row 125 combines the value in row 124, with the breaker type in row 117.

I have tried the index formula, but I am not very familiar with it , and not sure if it would work with this.

I have attached one of the files, this one is a 84 ckt 480/277v 3ph 4w panel.

Any ideas are greatly appreciated,

Troy Dawes

Hi

Where do you want to list the non 0 items?

rylo

For now, I would like to list the non-zeros just below the list.
I can adjust the location if needed. What formula are you thinking might work?

Thanks for the help,
Troy

Hi

BQ127:

Please Login or Register  to view this content.

This formula has to be array entered (ctrl, shift, enter).

Copy from BQ127 down as required. It will bring back the non 0 items, in column form.

HTH

rylo

Thanks, it looks great.

Troy