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 |
Select cell containing specific text &return value from another ce
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 |
Select cell containing specific text &return value from anothe
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 |
Select cell containing specific text &return value from anothe
=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 |
Select cell containing specific text &return value from anothe
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 |
All times are GMT +1. The time now is 07:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com