Find column based on header, search it for value, and return row number of found value [on hold]
up vote
0
down vote
favorite
I'm trying to write an excel formula that can get the value of a cell based on the value in a corresponding cell in the same row. Here's what I have so far:
=INDIRECT(ADDRESS({Need to figure this part out},MATCH("grptext",grp!1:1)))
I've already found the way to get the value I want to search for. The formula should then find the correct column to search in by searching the names of column headers, then search the found column for the value, and then return the row number of the found value so the address function can be completed. I'm not trying to use VBA, I just want to be able to paste this in an excel cell.
Thanks!
microsoft-excel
put on hold as too broad by fixer1234, PeterH, bertieb, Toto, music2myear Nov 15 at 20:17
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
0
down vote
favorite
I'm trying to write an excel formula that can get the value of a cell based on the value in a corresponding cell in the same row. Here's what I have so far:
=INDIRECT(ADDRESS({Need to figure this part out},MATCH("grptext",grp!1:1)))
I've already found the way to get the value I want to search for. The formula should then find the correct column to search in by searching the names of column headers, then search the found column for the value, and then return the row number of the found value so the address function can be completed. I'm not trying to use VBA, I just want to be able to paste this in an excel cell.
Thanks!
microsoft-excel
put on hold as too broad by fixer1234, PeterH, bertieb, Toto, music2myear Nov 15 at 20:17
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
This will be pretty complex, you'll need to search for something to get the column and translate that to a range to search. What about several formulas across some helper columns?
– Raystafarian
Feb 20 '15 at 1:55
That sounds like a good idea for development. Is there a reason why I wouldn't be able to consolidate the formulas into one cell after I've successfully created the formulas in the helper columns?
– DFrancisFan
Feb 20 '15 at 5:01
So how are you referencing the first value (to match headers) and then the second value to find in the column of the matching header?
– Raystafarian
Feb 20 '15 at 13:06
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm trying to write an excel formula that can get the value of a cell based on the value in a corresponding cell in the same row. Here's what I have so far:
=INDIRECT(ADDRESS({Need to figure this part out},MATCH("grptext",grp!1:1)))
I've already found the way to get the value I want to search for. The formula should then find the correct column to search in by searching the names of column headers, then search the found column for the value, and then return the row number of the found value so the address function can be completed. I'm not trying to use VBA, I just want to be able to paste this in an excel cell.
Thanks!
microsoft-excel
I'm trying to write an excel formula that can get the value of a cell based on the value in a corresponding cell in the same row. Here's what I have so far:
=INDIRECT(ADDRESS({Need to figure this part out},MATCH("grptext",grp!1:1)))
I've already found the way to get the value I want to search for. The formula should then find the correct column to search in by searching the names of column headers, then search the found column for the value, and then return the row number of the found value so the address function can be completed. I'm not trying to use VBA, I just want to be able to paste this in an excel cell.
Thanks!
microsoft-excel
microsoft-excel
asked Feb 19 '15 at 23:07
DFrancisFan
28114
28114
put on hold as too broad by fixer1234, PeterH, bertieb, Toto, music2myear Nov 15 at 20:17
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
put on hold as too broad by fixer1234, PeterH, bertieb, Toto, music2myear Nov 15 at 20:17
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
This will be pretty complex, you'll need to search for something to get the column and translate that to a range to search. What about several formulas across some helper columns?
– Raystafarian
Feb 20 '15 at 1:55
That sounds like a good idea for development. Is there a reason why I wouldn't be able to consolidate the formulas into one cell after I've successfully created the formulas in the helper columns?
– DFrancisFan
Feb 20 '15 at 5:01
So how are you referencing the first value (to match headers) and then the second value to find in the column of the matching header?
– Raystafarian
Feb 20 '15 at 13:06
add a comment |
This will be pretty complex, you'll need to search for something to get the column and translate that to a range to search. What about several formulas across some helper columns?
– Raystafarian
Feb 20 '15 at 1:55
That sounds like a good idea for development. Is there a reason why I wouldn't be able to consolidate the formulas into one cell after I've successfully created the formulas in the helper columns?
– DFrancisFan
Feb 20 '15 at 5:01
So how are you referencing the first value (to match headers) and then the second value to find in the column of the matching header?
– Raystafarian
Feb 20 '15 at 13:06
This will be pretty complex, you'll need to search for something to get the column and translate that to a range to search. What about several formulas across some helper columns?
– Raystafarian
Feb 20 '15 at 1:55
This will be pretty complex, you'll need to search for something to get the column and translate that to a range to search. What about several formulas across some helper columns?
– Raystafarian
Feb 20 '15 at 1:55
That sounds like a good idea for development. Is there a reason why I wouldn't be able to consolidate the formulas into one cell after I've successfully created the formulas in the helper columns?
– DFrancisFan
Feb 20 '15 at 5:01
That sounds like a good idea for development. Is there a reason why I wouldn't be able to consolidate the formulas into one cell after I've successfully created the formulas in the helper columns?
– DFrancisFan
Feb 20 '15 at 5:01
So how are you referencing the first value (to match headers) and then the second value to find in the column of the matching header?
– Raystafarian
Feb 20 '15 at 13:06
So how are you referencing the first value (to match headers) and then the second value to find in the column of the matching header?
– Raystafarian
Feb 20 '15 at 13:06
add a comment |
3 Answers
3
active
oldest
votes
up vote
0
down vote
Can you use the index match function?
= INDEX ( entire matrix , MATCH ( vertical lookup value, entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )
or
=ADDRESS(MATCH("horizontal item to find",A1:A6,0),MATCH("vertical item to find",A2:E2,0))
or
=ADDRESS(ROW(),MATCH("vertical item to find",A1:E1,0))
add a comment |
up vote
0
down vote
I think what you're trying to do can be accomplished with something like this -
=VLOOKUP(2,INDIRECT(ADDRESS(1,MATCH("b",A1:C1,0))):$C$10,2,0)
This is searching for 2
in the column where the header is b
and returning the match to the right of it. It assumes the headers are somewhere in A1:C1
and the entire array for searching is A1:C10
. Change as needed.
It will error if there's no column to the right of what it finds. It will error if the value isn't found.
add a comment |
up vote
0
down vote
It's actually not so complex. I'm going to assume your data starts at cell A1
so all your headers are in the first row. Here's something to return the first row number in which "SearchText" is found in the field called grptext
, wherever that may be:
=MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0)
I abhor the INDIRECT
function and I'm betting there's an easier way to get at the data you actually want but I can't tell what that is. The above will give you a row number but you're going to use that to get different data. You can get that directly using INDEX
+MATCH
.
Let's say you want the data from the field ReturnData
that's in the same row as the "SearchText" was found in the field grptext
. If you don't know where ReturnData
is, then you can use the same trick twice:
=INDEX(OFFSET($A:$A,0,MATCH("ReturnData",$1:$1,0)-1),MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
If you already know where ReturnData
is, it only gets easier. If it's in C:C
, then this:
=INDEX($C:$C,MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Can you use the index match function?
= INDEX ( entire matrix , MATCH ( vertical lookup value, entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )
or
=ADDRESS(MATCH("horizontal item to find",A1:A6,0),MATCH("vertical item to find",A2:E2,0))
or
=ADDRESS(ROW(),MATCH("vertical item to find",A1:E1,0))
add a comment |
up vote
0
down vote
Can you use the index match function?
= INDEX ( entire matrix , MATCH ( vertical lookup value, entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )
or
=ADDRESS(MATCH("horizontal item to find",A1:A6,0),MATCH("vertical item to find",A2:E2,0))
or
=ADDRESS(ROW(),MATCH("vertical item to find",A1:E1,0))
add a comment |
up vote
0
down vote
up vote
0
down vote
Can you use the index match function?
= INDEX ( entire matrix , MATCH ( vertical lookup value, entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )
or
=ADDRESS(MATCH("horizontal item to find",A1:A6,0),MATCH("vertical item to find",A2:E2,0))
or
=ADDRESS(ROW(),MATCH("vertical item to find",A1:E1,0))
Can you use the index match function?
= INDEX ( entire matrix , MATCH ( vertical lookup value, entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )
or
=ADDRESS(MATCH("horizontal item to find",A1:A6,0),MATCH("vertical item to find",A2:E2,0))
or
=ADDRESS(ROW(),MATCH("vertical item to find",A1:E1,0))
edited Feb 20 '15 at 13:05
Raystafarian
19.3k104989
19.3k104989
answered Feb 20 '15 at 9:37
Graham
1
1
add a comment |
add a comment |
up vote
0
down vote
I think what you're trying to do can be accomplished with something like this -
=VLOOKUP(2,INDIRECT(ADDRESS(1,MATCH("b",A1:C1,0))):$C$10,2,0)
This is searching for 2
in the column where the header is b
and returning the match to the right of it. It assumes the headers are somewhere in A1:C1
and the entire array for searching is A1:C10
. Change as needed.
It will error if there's no column to the right of what it finds. It will error if the value isn't found.
add a comment |
up vote
0
down vote
I think what you're trying to do can be accomplished with something like this -
=VLOOKUP(2,INDIRECT(ADDRESS(1,MATCH("b",A1:C1,0))):$C$10,2,0)
This is searching for 2
in the column where the header is b
and returning the match to the right of it. It assumes the headers are somewhere in A1:C1
and the entire array for searching is A1:C10
. Change as needed.
It will error if there's no column to the right of what it finds. It will error if the value isn't found.
add a comment |
up vote
0
down vote
up vote
0
down vote
I think what you're trying to do can be accomplished with something like this -
=VLOOKUP(2,INDIRECT(ADDRESS(1,MATCH("b",A1:C1,0))):$C$10,2,0)
This is searching for 2
in the column where the header is b
and returning the match to the right of it. It assumes the headers are somewhere in A1:C1
and the entire array for searching is A1:C10
. Change as needed.
It will error if there's no column to the right of what it finds. It will error if the value isn't found.
I think what you're trying to do can be accomplished with something like this -
=VLOOKUP(2,INDIRECT(ADDRESS(1,MATCH("b",A1:C1,0))):$C$10,2,0)
This is searching for 2
in the column where the header is b
and returning the match to the right of it. It assumes the headers are somewhere in A1:C1
and the entire array for searching is A1:C10
. Change as needed.
It will error if there's no column to the right of what it finds. It will error if the value isn't found.
answered Feb 20 '15 at 13:17
Raystafarian
19.3k104989
19.3k104989
add a comment |
add a comment |
up vote
0
down vote
It's actually not so complex. I'm going to assume your data starts at cell A1
so all your headers are in the first row. Here's something to return the first row number in which "SearchText" is found in the field called grptext
, wherever that may be:
=MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0)
I abhor the INDIRECT
function and I'm betting there's an easier way to get at the data you actually want but I can't tell what that is. The above will give you a row number but you're going to use that to get different data. You can get that directly using INDEX
+MATCH
.
Let's say you want the data from the field ReturnData
that's in the same row as the "SearchText" was found in the field grptext
. If you don't know where ReturnData
is, then you can use the same trick twice:
=INDEX(OFFSET($A:$A,0,MATCH("ReturnData",$1:$1,0)-1),MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
If you already know where ReturnData
is, it only gets easier. If it's in C:C
, then this:
=INDEX($C:$C,MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
add a comment |
up vote
0
down vote
It's actually not so complex. I'm going to assume your data starts at cell A1
so all your headers are in the first row. Here's something to return the first row number in which "SearchText" is found in the field called grptext
, wherever that may be:
=MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0)
I abhor the INDIRECT
function and I'm betting there's an easier way to get at the data you actually want but I can't tell what that is. The above will give you a row number but you're going to use that to get different data. You can get that directly using INDEX
+MATCH
.
Let's say you want the data from the field ReturnData
that's in the same row as the "SearchText" was found in the field grptext
. If you don't know where ReturnData
is, then you can use the same trick twice:
=INDEX(OFFSET($A:$A,0,MATCH("ReturnData",$1:$1,0)-1),MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
If you already know where ReturnData
is, it only gets easier. If it's in C:C
, then this:
=INDEX($C:$C,MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
add a comment |
up vote
0
down vote
up vote
0
down vote
It's actually not so complex. I'm going to assume your data starts at cell A1
so all your headers are in the first row. Here's something to return the first row number in which "SearchText" is found in the field called grptext
, wherever that may be:
=MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0)
I abhor the INDIRECT
function and I'm betting there's an easier way to get at the data you actually want but I can't tell what that is. The above will give you a row number but you're going to use that to get different data. You can get that directly using INDEX
+MATCH
.
Let's say you want the data from the field ReturnData
that's in the same row as the "SearchText" was found in the field grptext
. If you don't know where ReturnData
is, then you can use the same trick twice:
=INDEX(OFFSET($A:$A,0,MATCH("ReturnData",$1:$1,0)-1),MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
If you already know where ReturnData
is, it only gets easier. If it's in C:C
, then this:
=INDEX($C:$C,MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
It's actually not so complex. I'm going to assume your data starts at cell A1
so all your headers are in the first row. Here's something to return the first row number in which "SearchText" is found in the field called grptext
, wherever that may be:
=MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0)
I abhor the INDIRECT
function and I'm betting there's an easier way to get at the data you actually want but I can't tell what that is. The above will give you a row number but you're going to use that to get different data. You can get that directly using INDEX
+MATCH
.
Let's say you want the data from the field ReturnData
that's in the same row as the "SearchText" was found in the field grptext
. If you don't know where ReturnData
is, then you can use the same trick twice:
=INDEX(OFFSET($A:$A,0,MATCH("ReturnData",$1:$1,0)-1),MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
If you already know where ReturnData
is, it only gets easier. If it's in C:C
, then this:
=INDEX($C:$C,MATCH("SearchText",OFFSET($A:$A,0,MATCH("grptext",$1:$1,0)-1),0))
answered Feb 20 '15 at 13:23
Engineer Toast
2,8781828
2,8781828
add a comment |
add a comment |
This will be pretty complex, you'll need to search for something to get the column and translate that to a range to search. What about several formulas across some helper columns?
– Raystafarian
Feb 20 '15 at 1:55
That sounds like a good idea for development. Is there a reason why I wouldn't be able to consolidate the formulas into one cell after I've successfully created the formulas in the helper columns?
– DFrancisFan
Feb 20 '15 at 5:01
So how are you referencing the first value (to match headers) and then the second value to find in the column of the matching header?
– Raystafarian
Feb 20 '15 at 13:06