Pull comments based on 3 criteria

kurigami

New Member
Joined
Aug 18, 2016
Messages
15
Hi everyone,

This issue has been stumping me and I'm at a loss of how to proceed. I'm trying to pull comments from Microsoft Forms results which are dumping into a 2nd sheet on my workbook (Form1). Here's how things are arranged.

'Readout Data'!B3: Name
'Readout Data'!A2: Start date (date format ex. 5/13/2020)
'Readout Data'!B2: End date (date format ex. 5/13/2020)

I'd like to retrieve the comments from Form1!X:X (the comments field) from my table when the name ('Readout Data'!B3) matches the value in Form1!G:G, and the start and end dates ('Readout Data'!A2 & B2) are within the DateTime value on Form1!B:B.

Initially I was using TEXTJOIN in a merged cell to pull in the values and apply a line break between the results. This works perfectly until the results exceeded the character limit of the merged cell/TEXTJOIN formula and gives me a #Value error. Here's what was working until I hit the limit:
="• "&TEXTJOIN(CHAR(10)&"• ",TRUE,IF(($B$3=Form1!G:G)*(Form1!B:B>=A2)*(Form1!B:B<=B2+1),Form1!X:X,""))

To resolve the issue, I've un-merged the range, and have shifted to using an Index Match Array, but it's failing to find any of the comments based on my criteria (getting #N/A):
{=INDEX(Form1!$X:$X,MATCH(1,($B$3=Form1!G:G)*($A$2>=Form1!B:B)*($B$2+1<=Form1!C:C),0))}

Hoping someone might know how I can adjust my Index Match Array to find the results or even a different formula to use. The only limitation I know I have is I cannot use VBA as a solution because this is all contained on an Excel Online file so the Forms Results can continue dumping the results into the sheet automatically as they're submitted.

Thanks everyone!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

kurigami

New Member
Joined
Aug 18, 2016
Messages
15
Not sure how to edit my question, but I've gotten slightly closer to my answer. I've updated my formula to match the dates correctly, but I'm only getting the first result over and over again, instead of getting each comment that matches the other criteria. Here's the updated formula:
{=INDEX(Form1!$X:$X,MATCH(1,($B$3=Form1!G:G)*(Form1!B:B>=$A$2)*(Form1!C:C<=$B$2+1),0))}

I just need to know how to have this formula pull all comments that match the 3 criteria instead of only pulling the first one.
 

kurigami

New Member
Joined
Aug 18, 2016
Messages
15
Guess I can't delete my post, so I'll put in the resolution I found here in case anyone else needs it:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,542
Messages
5,625,416
Members
416,101
Latest member
CCoetzee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top