index match in Array

[nickusami]

Hi everybody,

I’m trying to make if error index match multiple criteria work on this example:

A tab called data has a column “A” lists several items, and Row “1” with punch of dates projects throughout the year. Under each date there is a value corresponds with item in column “A”.

What I need is in another tab called “REP”, I like to list the only the item that has a value of bigger than 0 , and, also corresponds to the date that will change frequently in Row “1”

Please see attached example.

Data.JPG

As a result in tab REP, I like to see cells highlighted yellow, and then values highlighted grayish.

Rep.JPG

I appreciate your help

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Are there going to be any dates where there is NOT at least one non zero value?

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[nickusami]

Sure, but for some reason not going through. I 'll try over and over till i get it attached.

[nickusami]

Please Try it. I see it attached now

[hemesh]

do you want zero's not to be shown then try below in B2

Please Login or Register  to view this content.

drag down and to right

[nickusami]

thanks Hemesh.
it works great at populating the numbers (This need in step 2).
But first, I need to generate list of items in tab "REP" under Column "A" that will contain numbers bigger than 0. the yellow column in tab "Rep" should be formatted too.

basically the format should have 2 conditions. once if the the value in array B2:S12 in tab "DATA" is bigger than 0, then the other condition if the dates in B1:S1 in tab "DATA" matches the date given in tab B1:E1 in tab "REP"

[BMV]

For list - array formula

Please Login or Register  to view this content.

And for data

Please Login or Register  to view this content.

[nickusami]

Thank you BMV,
for some reason, step 1 to list items, didn't work. it did list everything in that column. apparently the filter or criteria did not work.
step 2 to return the values, worked great, but with little issue. if one of the item removed from Column A in Data tab, it will return value of 0 in tab Rep. when the second formula read 0 for the item, it will return the value to #N/A.
is there any way to use if error to remove the #N/A and keep it blank.
i guess if my formula is step 1 worked, I will not see this issue with #N/A.
Thanks

[nickusami]

Actually BMV, I just fixed the issue on #N/A. i added iferror to it and worked fine. I appreciate your help