Gfrktyl

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!

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))

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.

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))