Here's a fun one I'm trying to work out. I'll try to spare you the unneccesary details.
I'm having some trouble writing an equation to retrieve a range from conditionally from several matching pieces of information. The problem is I am pulling this from a DataCube so the spacing/ format is kind of janky. (Please see mock attachment)
I've made some drop down boxes and when the criteria is met, I would like it to display the relevant customers.
Here's what I'm thinking- I can specify the next column's range as a match function. I ALWAYS have "Total" after each item, so I'm thinking I could do something for the final offset to display match(Color Total)- match(Color) for the number of rows to display....once we've made it to that column. Getting there on the other hand...I've been trying to use concatenate with match to specify the rows to search (not sure if this is allowed). Here's what I have so far:
=OFFSET(A1,MATCH(I6,"C"&MATCH(I5,"B"&MATCH(Category,A:A,0)&":"&"B"&MATCH(Category&" Total",A:A,0),0)&":"&"C"&MATCH(Model&" Total","B"&MATCH(Category,A:A,0)&":"&"B"&MATCH(Category&" Total",A:A,0),0),0),1,"C"&MATCH(Model&" Total","B"&MATCH(Category,A:A,0)&":"&"B"&MATCH(Category&" Total",A:A,0),0)-"C"&MATCH(Model,"B"&MATCH(I4,A:A,0)&":"&"B"&MATCH(Category&" Total",A:A,0),0),1)
Bookmarks