Multiple index+match formulas to get non-zero value data from column and return row value

It would help if you attached a sample Excel workbook, rather than a picture of one (which I can't read on my monitor !).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it is broken on this forum.

Hope this helps.

Pete

If this was just for DI, then you could use a helper column in the Raw Data sheet (e.g. column S), with this formula in S2:

=IF(Backlog[[#This Row],[DI]]=0,"-",MAX(S$1:S1)+1)

then copy this down to the bottom of your data. Then in the DI_summary sheet, you could put this formula in C5:

=IFERROR(INDEX('Raw Data'!A:A,MATCH(ROWS($1:1),'Raw Data'!$S:$S,0)),"")

You could copy this into D5:E5, but I'm not sure where the data for columns F or G is expected to come from. Then copy those formulae down as far as is necessary. If you want just the 3-letter month in column C, you would put RIGHT( … ,3) around the formula in C5.

You could repeat this for the other business units, where you could copy the headings from E1:M1 of the Raw Data sheet across to Q1 (which is why my helper was in S2) and then have similar formulae for those helper columns. The formula in each summary sheet will be very similar to that posted above, although the second range (shown in red above) would need to be changed to suit the appropriate helper.

Hope this helps.

Pete

Pete -

The if formula didn't quite work properly as it resulted in a "1" when there was a value instead of the value itself from the backlog table. I have attached the sample file again and hardcoded the DI Summary tab to show how the results of the formula should show. The iferror worked well to return the months however not sure how to apply them for the other columns. See if you can replicate the hardcoded text in the DI Summary tab using formulas and I can update the formulas for the other product lines. Greatly appreciate your help!

Originally Posted by excel-lent13

... See if you can replicate the hardcoded text in the DI Summary tab using formulas and I can update the formulas for the other product lines...

The file you posted has exactly the same results as mine - see attached. The formula in column F needs to have the column ref adjusted to suit the DI column.

Hope this helps.

Pete

Originally Posted by Pete_UK

The file you posted has exactly the same results as mine - see attached. The formula in column F needs to have the column ref adjusted to suit the DI column.

Hope this helps.

Pete

Nevermind Glenn, Pete's file works with negative values. Thank you Pete!

Glenn - you are absolutely a genius! Thank you for updating the file.

Pete - Thank you to you as well!

Glenn - any way to modify the formula so it picks up negatives as well? Basically, it needs to pick up non-zeros. Below is what you currently have:

IFERROR(INDEX('Raw Data'!A:A,AGGREGATE(15,6,ROW(Backlog[[DI]:[DI]])/(Backlog[[DI]:[DI]]>0),ROWS($1:1))),"")

return value of a negative number is a zero. See attached file.