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.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style> 9:00 AM 9:15 AM 9:30 AM 9:45 AM 10:00 AM 10:15 AM 10:30 AM 10:45 AM 11:00 AM 11:15 AM 11:30 AM 11:45 AM 12:00 PM Value to Look Up Result 1 Result 2 Result 3 Many rows with other data that cannot be moved live here. sadly Mike P P P P P B P P P P P L L L Sue P P P P B P P P P L L P P Example Bob P B P P P P B P P P L L P L 11:30 AM 11:45 AM I want to enter a value in column P and have the values from the Named Range 'TIMES' B1:N1 reflect in the columns Q,R,S,T,U etc... <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> </tbody>

<tbody>
</tbody>

<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.