Index-CSE: Can more than one value (text) be returned from a vertical lookup column

[alipete122]

Long time reader, first time poster. Thanks for all of the quality questions and answers.

I am using an Index-CSE Fx to return whole rows of data from a source table (time log) into three separate sub-tables based on a selected "category column" which is my vertical lookup column (i.e. "Port Time" or "Weather"). This has worked exactly as intended as long as I am referencing a single value in the column. However, in the third and final sub-table, I would like to return three separate values to the sub-table as they might occur in the source table (i.e. "Pumping" and/or "Vessel Delay" and/or Shore Delay"). This has me completely stumped. Can anyone advise me if and how I can reference two or more values from the SAME vertical lookup column, so that they return the sub-table as they occur each time. Some further details below:

"Port Time" - Works as intended
=IFERROR(INDEX(TimeLog!A$2:A$20,SMALL(IF(TimeLog!$B$2:$B$20=Analysis!$A$1,ROW(TimeLog!A$2:A$20)-ROW(TimeLog!A$2)+1),ROWS(TimeLog!A$2:TimeLog!A2))),"")


"Weather" - Works as intended
=IFERROR(INDEX(TimeLog!A$2:A$20,SMALL(IF(TimeLog!$B$2:$B$20=Analysis!$A$24,ROW(TimeLog!A$2:A$20)-ROW(TimeLog!A$2)+1),ROWS(TimeLog!A$2:TimeLog!A2))),"")


"Pumping" and/or "Shore Delay" and/or "Vessel Delay" - Does not work as intended
=IFERROR(INDEX(TimeLog!A$2:A$20,SMALL(IF(TimeLog!$B$2:$B$20=Analysis!$G$49:$G$51,ROW(TimeLog!A$2:A$20)-ROW(TimeLog!A$2)+1),ROWS(TimeLog!A$2:TimeLog!A2))),"")

Thanks for your time

 

[sergioMabres]

Hi Pete
I am no sure how your TimeLog data is organized but I guess to retrieve 3 values you will need 3 formulas like Port Time formula, even if you can later concatenate the 3 values into one cell if you need so, like in =FormulaForPumping & FormulaForShoreDelay & FormulaForVesselDelay
Cheers
Sergio

 

[Aladin Akyurek]

Control+shift+enter and copy down:

=IFERROR(INDEX(TimeLog!A$2:A$20,SMALL(IF(ISNUMBER(MATCH(TimeLog!$B$2:$B$20,Analysis!$G$49:$G$51,0)),ROW(TimeLog!A$2:A$20)-ROW(TimeLog!A$2)+1),ROWS(TimeLog!A$2:A2))),"")

Are you implementing these formulas in A2 of TimeLog or somewhere else?

 

[alipete122]

Good day Aladin,

Would be implemented into a stand alone table starting at first column, first cell after header. Then pulled over and down. Am VERY pleased to advise that it worked perfectly. Thanks VERY much for your solution. Sergio, thank you as well for your interest and time.

Pete

 

[Aladin Akyurek]

Good day Aladin,

Would be implemented into a stand alone table starting at first column, first cell after header. Then pulled over and down. Am VERY pleased to advise that it worked perfectly. Thanks VERY much for your solution. Sergio, thank you as well for your interest and time.

Pete

You are welcome.

Let's say that you implement the formula in A2 of Results... It's better to anchor the formula to A2 of this sheet...

=IFERROR(INDEX(TimeLog!A$2:A$20,SMALL(IF(ISNUMBER(MATCH(TimeLog!$B$2:$B$20,Analysis!$G$49:$G$51,0)),ROW(TimeLog!A$2:A$20 )-ROW(TimeLog!A$2)+1),ROWS(A$2:A2))),"")