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