Return a column value from a horizontal search
Hello
I'm trying to look across a row for a non blank cell and return what is in the first cell of the column in which the non blank cell is found. Does anybody have any ideas how that could be done? Thanks |
Return a column value from a horizontal search
The below formula returns the first value in Col A
=INDEX(A:A,MATCH("*",A:A,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Hello I'm trying to look across a row for a non blank cell and return what is in the first cell of the column in which the non blank cell is found. Does anybody have any ideas how that could be done? Thanks |
Return a column value from a horizontal search
First value of row 1 within A1:Z1
=INDEX(A1:Z1,0,MATCH("*",A1:Z1,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Hello I'm trying to look across a row for a non blank cell and return what is in the first cell of the column in which the non blank cell is found. Does anybody have any ideas how that could be done? Thanks |
Return a column value from a horizontal search
Thank you for that however this returns the cell A1 entry even if the cell is
blank. I need it to check which cell in the row is not empty and return the relevant column entry in row 1 "Jacob Skaria" wrote: The below formula returns the first value in Col A =INDEX(A:A,MATCH("*",A:A,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Hello I'm trying to look across a row for a non blank cell and return what is in the first cell of the column in which the non blank cell is found. Does anybody have any ideas how that could be done? Thanks |
Return a column value from a horizontal search
I misunderstood your query as 1st column instead of 1st Row. The below will
return the first non-blank entry in row1 =INDEX(1:1,0,MATCH("*",1:1,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Thank you for that however this returns the cell A1 entry even if the cell is blank. I need it to check which cell in the row is not empty and return the relevant column entry in row 1 "Jacob Skaria" wrote: The below formula returns the first value in Col A =INDEX(A:A,MATCH("*",A:A,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Hello I'm trying to look across a row for a non blank cell and return what is in the first cell of the column in which the non blank cell is found. Does anybody have any ideas how that could be done? Thanks |
Return a column value from a horizontal search
Thanks but again it doesn't seem to be working, it seems to return the colum
header for the first cell whether blank or not. Please find below an example of what I'm trying to achieve with column 'a' being the formula column and row '1' the value to return: a b c d e 1 one two three four 2 four 1 3 three 1 4 5 6 Four 1 "Jacob Skaria" wrote: I misunderstood your query as 1st column instead of 1st Row. The below will return the first non-blank entry in row1 =INDEX(1:1,0,MATCH("*",1:1,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Thank you for that however this returns the cell A1 entry even if the cell is blank. I need it to check which cell in the row is not empty and return the relevant column entry in row 1 "Jacob Skaria" wrote: The below formula returns the first value in Col A =INDEX(A:A,MATCH("*",A:A,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Hello I'm trying to look across a row for a non blank cell and return what is in the first cell of the column in which the non blank cell is found. Does anybody have any ideas how that could be done? Thanks |
Return a column value from a horizontal search
With the below data...
In A2 enter the below formula and copy that down. This will look for an entry 1. =INDEX($A$1:$Z$1,0,MATCH(1,$B2:$Z2,0)+1) If you are entering a text value then instead of one then use the below in A2 =INDEX($A$1:$Z$1,0,MATCH("*",$B2:$Z2,0)+1) -- If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Thanks but again it doesn't seem to be working, it seems to return the colum header for the first cell whether blank or not. Please find below an example of what I'm trying to achieve with column 'a' being the formula column and row '1' the value to return: a b c d e 1 one two three four 2 four 1 3 three 1 4 5 6 Four 1 "Jacob Skaria" wrote: I misunderstood your query as 1st column instead of 1st Row. The below will return the first non-blank entry in row1 =INDEX(1:1,0,MATCH("*",1:1,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Thank you for that however this returns the cell A1 entry even if the cell is blank. I need it to check which cell in the row is not empty and return the relevant column entry in row 1 "Jacob Skaria" wrote: The below formula returns the first value in Col A =INDEX(A:A,MATCH("*",A:A,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Hello I'm trying to look across a row for a non blank cell and return what is in the first cell of the column in which the non blank cell is found. Does anybody have any ideas how that could be done? Thanks |
Return a column value from a horizontal search
Hi,
You can try this array formula (Ctrl+Shift+Enter) =INDEX(E6:E10,MATCH(TRUE,E6:E10<"",0),1) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Spag" wrote in message ... Hello I'm trying to look across a row for a non blank cell and return what is in the first cell of the column in which the non blank cell is found. Does anybody have any ideas how that could be done? Thanks |
Return a column value from a horizontal search
Thank you very much that works perfectly.
"Jacob Skaria" wrote: With the below data... In A2 enter the below formula and copy that down. This will look for an entry 1. =INDEX($A$1:$Z$1,0,MATCH(1,$B2:$Z2,0)+1) If you are entering a text value then instead of one then use the below in A2 =INDEX($A$1:$Z$1,0,MATCH("*",$B2:$Z2,0)+1) -- If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Thanks but again it doesn't seem to be working, it seems to return the colum header for the first cell whether blank or not. Please find below an example of what I'm trying to achieve with column 'a' being the formula column and row '1' the value to return: a b c d e 1 one two three four 2 four 1 3 three 1 4 5 6 Four 1 "Jacob Skaria" wrote: I misunderstood your query as 1st column instead of 1st Row. The below will return the first non-blank entry in row1 =INDEX(1:1,0,MATCH("*",1:1,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Thank you for that however this returns the cell A1 entry even if the cell is blank. I need it to check which cell in the row is not empty and return the relevant column entry in row 1 "Jacob Skaria" wrote: The below formula returns the first value in Col A =INDEX(A:A,MATCH("*",A:A,0)) If this post helps click Yes --------------- Jacob Skaria "Spag" wrote: Hello I'm trying to look across a row for a non blank cell and return what is in the first cell of the column in which the non blank cell is found. Does anybody have any ideas how that could be done? Thanks |
All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com