I need to lookup and returm multiple results when the column data is not zero.
Attached is a sample worksheet with the desired results. I need a fomular in Ordering Trend on Column B and Column C. Can this be done using fomular or need a VBA?
I need to lookup and returm multiple results when the column data is not zero.
Attached is a sample worksheet with the desired results. I need a fomular in Ordering Trend on Column B and Column C. Can this be done using fomular or need a VBA?
Try this in B2:
=IFERROR(INDEX(OrderMonth,AGGREGATE(15,6,COLUMN(OrderMonth)
/(INDEX(Data!$B$2:$M$6,MATCH($A$2,Customer,0),0)<>0),ROWS($A$2:A2))-1),"")
in C2:
=IF(B2="","",INDEX(Data!$B$2:$M$6,MATCH($A$2,Customer,0),MATCH(B2,OrderMonth,0))
Put this on B2 and ENTERD as ARRAY FORMULAS, then copied down until blanks:
=IFERROR(INDEX(Data!$B$1:$M$1,SMALL(IF(INDEX(Data!$B$2:$M$6,MATCH(A$2,Data!$A$2:$A$6,0),0)<>0,COLUMN(Data!$B$1:$M$1)-COLUMN(Data!$B$1)+1),ROWS($A$1:A1))),"")
And this on C2 then copied down:
=IFERROR(INDEX(Data!$B$2:$M$6,MATCH($A$2,Data!$A$2:$A$6,0),MATCH($B2,Data!$B$1:$M$1,0)),"")
Thank you Phuocam and azumi, Phuocam's fomular works and azumi's file cannot open and the fomular works on row 1 only. I shall close this.
Dear Phuocam, may I know what does these 2 numbers 15, 6 means in "AGGREGATE(15,6,COLUMN(OrderMonth)" ? My original data column starts from K to AV, and the data row starts from row 8 to 400.
See this link:
https://support.office.com/en-us/art...6-e19993fa26df
Read this about the AGGREGATE function https://support.office.com/en-us/art...6-e19993fa26df
Thank you, Phuocam.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks