Lookup with multiple results in Google Sheets

[hemitheus]

I wish Excel were an option, but it isnt.

I need to be able to have a formula that looks at the data i enter in column P and references the table to the left and returns all of the times (Row 1) with matching results in the table.

Thank you in advance for your assistance.
[TABLE="width: 500"]
<tbody>[TR]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 84px"><col width="48"><col width="48"><col width="48"><col width="48"><col width="54"><col width="54"><col width="54"><col width="54"><col width="54"><col width="54"><col width="54"><col width="54"><col width="53"><col width="32"><col width="106"><col width="55"><col width="55"><col width="55"></colgroup><tbody>[TR]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]9:00 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]9:15 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]9:30 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]9:45 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]10:00 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]10:15 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]10:30 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]10:45 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]11:00 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]11:15 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]11:30 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]11:45 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]12:00 PM[/TD]
[TD][/TD]
[TD]Value to Look Up[/TD]
[TD="align: center"]Result 1[/TD]
[TD="align: center"]Result 2[/TD]
[TD="align: center"]Result 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , colspan: 13, align: center"]Many rows with other data that cannot be moved live here. sadly[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]
[TD="align: center"]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Example Bob[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]P[/TD]
[TD][/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]11:30 AM[/TD]
[TD="align: center"]11:45 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]I want to enter a value in column P and have the values from the

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Named Range 'TIMES'

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "][/TD]
[TD]B1:N1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]reflect in the columns Q,R,S,T,U etc...

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[estevaoba]

Array enter (ctrl+shift+enter) this for first result:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IFERROR(INDEX($A$2:$N$100,1,SMALL(IF($A$5:$A$100=$P$3,IF($B$5:$N$100=$P$4,COLUMN($B$5:$N$5))),COLUMN(A$1))),"")[/TD]
[/TR]
</tbody>[/TABLE]

Copy across for the other results.

Considering P3=Bob and P4=L
Or you can drag those data to other cells.

Godspeed!

 

[hemitheus]

---

Thank you for the quick response.

In sheets it looks as follows:

=ArrayFormula(IFERROR(INDEX($A$2:$N$100,1,SMALL(IF($A$5:$A$100=$P$3,IF($B$5:$N$100=$P$4,COLUMN($B$5:$N$5))),COLUMN(A$1))),""))

I wish I could use Excel... Regardless. I entered this into Q3 as an array and entered the L in P3 and there was no result.

I modified your code to visually reflect an error as follows:

=ArrayFormula(IFERROR(INDEX($A$2:$N$100,1,SMALL(IF($A$5:$A$100=$P$3,IF($B$5:$N$100=$P$4,COLUMN($B$5:$N$5))),COLUMN(A$1))),"E R R O R"))

the result was Q3 was still blank.

Any thoughts?

 

[hemitheus]

Thank you,

I did use this and it, sadly, did not work.

As I am forced to use sheets, the formula translates to the following:

=ArrayFormula(IFERROR(INDEX($A$2:$N$100,1,SMALL(IF($A$5:$A$100=$P$3,IF($B$5:$N$100=$P$4,COLUMN($B$5:$N$5))),COLUMN(A$1))),""))

Since it did not function I modified it to visually see the iferror function, if it were firing to this:

=ArrayFormula(IFERROR(INDEX($A$2:$N$100,1,SMALL(IF($A$5:$A$100=$P$3,IF($B$5:$N$100=$P$4,COLUMN($B$5:$N$5))),COLUMN(A$1))),"E R R O R"))

The "e r r o r" did NOT show, so that eliminates that possibility.

Any suggestions?

 

[estevaoba]

Thank you for the quick response.
I entered this into Q3 as an array and entered the L in P3 and there was no result.

Check my first post.
L is in P4
Bob is in P3

Please get back.

 

[hemitheus]

I think that might be where the confusion is.

in column P the only entries possible will be blank, L, B, or P
Column A is for the names.

So each row is representative of the person in column A
Column P I would adjust in order to see what times the person had a L, B or P

It is also possible that you are right on the money with this and I am not following what you are saying.

Thanks

 

[estevaoba]

I changed cell ref for Name. Array enter this in Q5 and copy across and down:

[TABLE="width: 64"]
[TR]
  [TD="width: 64"]=IFERROR(INDEX($A$2:$N$100,1,SMALL(IF($A$5:$A$100=$A5,IF($B$5:$N$100=$P5,COLUMN($B$5:$N$5))),COLUMN(A$1))),"")[/TD]
[/TR]
[/TABLE]

That

 

[hemitheus]

I pasted your code directly into Q5 and made it an array formula. It did not yield any results.

 

[estevaoba]

I pasted your data in a sheet and made up this formula.

There must be some row/column arrangement different from yours, I´m afraid.

Please take a look at what I did in the link below and compare.

https://we.tl/INpB9ouHCN

 

[hemitheus]

Got it. and it works beautifully. THANK YOU! It must be Google Sheets.