Select cell containing specific text &return value from another ce

How do I find a cell in a row which contains a specific text string and
return a value (also a text string) from another cell in the same column?

For example: Can I search for first cell in Row 2 that contains the text
"Gate 1" and return the value "APR" from same column but different row?
Similarly, search for cell in Row 3 that contains "Gate 1" and return "MAY".

Col A Col B Col C
Row 1 APR MAY JUN
Row 2 Gate 1 Gate 3
Row 3 Gate 1

Thanks in advance,
Pam

You would use the MATCH() function to find the position of the first value in
the row that matches your criteria. Like so:

=MATCH("Gate 1",A2:L2,0)

Use the results of the MATCH() function in an INDEX() function to determine
which header value to retrieve, like this

=INDEX($A$1:$L$1,1,MATCH("Gate 1",A2:L2))

"plf100" wrote:

> How do I find a cell in a row which contains a specific text string and
> return a value (also a text string) from another cell in the same column?
>
> For example: Can I search for first cell in Row 2 that contains the text
> "Gate 1" and return the value "APR" from same column but different row?
> Similarly, search for cell in Row 3 that contains "Gate 1" and return "MAY".
>
> Col A Col B Col C
> Row 1 APR MAY JUN
> Row 2 Gate 1 Gate 3
> Row 3 Gate 1
>
> Thanks in advance,
> Pam
>
>
>

Thanks Duke, thats exactly what I needed.

Would I be able to include an IF() statement somewhere so that if none of
the cells in the row contain the text "Gate 1" then it would return a blank
("") rather than an error message?

"Duke Carey" wrote:

> You would use the MATCH() function to find the position of the first value in
> the row that matches your criteria. Like so:
>
> =MATCH("Gate 1",A2:L2,0)
>
> Use the results of the MATCH() function in an INDEX() function to determine
> which header value to retrieve, like this
>
> =INDEX($A$1:$L$1,1,MATCH("Gate 1",A2:L2))
>
> "plf100" wrote:
>
> > How do I find a cell in a row which contains a specific text string and
> > return a value (also a text string) from another cell in the same column?
> >
> > For example: Can I search for first cell in Row 2 that contains the text
> > "Gate 1" and return the value "APR" from same column but different row?
> > Similarly, search for cell in Row 3 that contains "Gate 1" and return "MAY".
> >
> > Col A Col B Col C
> > Row 1 APR MAY JUN
> > Row 2 Gate 1 Gate 3
> > Row 3 Gate 1
> >
> > Thanks in advance,
> > Pam
> >
> >
> >

=if(isna(INDEX($A$1:$L$1,1,MATCH("Gate
1",A2:L2,0))),"",INDEX($A$1:$L$1,1,MATCH("Gate 1",A2:L2,0)) )

should do it


"plf100" wrote:

> Thanks Duke, thats exactly what I needed.
>
> Would I be able to include an IF() statement somewhere so that if none of
> the cells in the row contain the text "Gate 1" then it would return a blank
> ("") rather than an error message?
>
> "Duke Carey" wrote:
>
> > You would use the MATCH() function to find the position of the first value in
> > the row that matches your criteria. Like so:
> >
> > =MATCH("Gate 1",A2:L2,0)
> >
> > Use the results of the MATCH() function in an INDEX() function to determine
> > which header value to retrieve, like this
> >
> > =INDEX($A$1:$L$1,1,MATCH("Gate 1",A2:L2))
> >
> > "plf100" wrote:
> >
> > > How do I find a cell in a row which contains a specific text string and
> > > return a value (also a text string) from another cell in the same column?
> > >
> > > For example: Can I search for first cell in Row 2 that contains the text
> > > "Gate 1" and return the value "APR" from same column but different row?
> > > Similarly, search for cell in Row 3 that contains "Gate 1" and return "MAY".
> > >
> > > Col A Col B Col C
> > > Row 1 APR MAY JUN
> > > Row 2 Gate 1 Gate 3
> > > Row 3 Gate 1
> > >
> > > Thanks in advance,
> > > Pam
> > >
> > >
> > >

Many thanks for your help Duke.

"Duke Carey" wrote:

> =if(isna(INDEX($A$1:$L$1,1,MATCH("Gate
> 1",A2:L2,0))),"",INDEX($A$1:$L$1,1,MATCH("Gate 1",A2:L2,0)) )
>
> should do it
>
>
> "plf100" wrote:
>
> > Thanks Duke, thats exactly what I needed.
> >
> > Would I be able to include an IF() statement somewhere so that if none of
> > the cells in the row contain the text "Gate 1" then it would return a blank
> > ("") rather than an error message?
> >
> > "Duke Carey" wrote:
> >
> > > You would use the MATCH() function to find the position of the first value in
> > > the row that matches your criteria. Like so:
> > >
> > > =MATCH("Gate 1",A2:L2,0)
> > >
> > > Use the results of the MATCH() function in an INDEX() function to determine
> > > which header value to retrieve, like this
> > >
> > > =INDEX($A$1:$L$1,1,MATCH("Gate 1",A2:L2))
> > >
> > > "plf100" wrote:
> > >
> > > > How do I find a cell in a row which contains a specific text string and
> > > > return a value (also a text string) from another cell in the same column?
> > > >
> > > > For example: Can I search for first cell in Row 2 that contains the text
> > > > "Gate 1" and return the value "APR" from same column but different row?
> > > > Similarly, search for cell in Row 3 that contains "Gate 1" and return "MAY".
> > > >
> > > > Col A Col B Col C
> > > > Row 1 APR MAY JUN
> > > > Row 2 Gate 1 Gate 3
> > > > Row 3 Gate 1
> > > >
> > > > Thanks in advance,
> > > > Pam
> > > >
> > > >
> > > >