Lookup Query - Occurrences

[lealea1982]

Hi there,

I'm struggling with a look-up type thing that I have on my worksheet. I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.

What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!

Many thanks

Leanne

[daddylonglegs]

Hello Leanne,

Assuming your data is in a worksheet called data, and that your list of months (in the same order) is in report worksheet, also in A2:A13 then try using this "array formula" in B2

=IF(COLUMNS($B2:B2)>COUNTIF(data!$B2:$H2,">0"),"",INDEX(data!$B$1:$H$1,SMALL(IF(data!$B2:$H2>0 ,COLUMN(data!$B2:$H2)-COLUMN(data!$B2)+1),COLUMNS($B2:B2))))

confirmed with CTRL+SHIFT+ENTER and copy down column and across as far as needed, when you run out of days where the value is above zero you get blanks

[lealea1982]

Perfect!! Thank you very much. :-)