CODEWITHSUNDEEP
excel
user2024449
user2024449

Reputation:

Excel: How to find the first row's value of a column depending on where to find a given number in another row of the same column

Imagine a 100x100 table. I have to find a given value in the first column. Then I have to check the row contains that given value, and I have to find the column where the value is 1 (every row has only one cell with value 1), and I need the first row's value of that column. I've tried several lookup functions (vlookup, hlookup, index match match, etc). No results. Is it possible using only functions and no VBA at all?

Upvotes: 0

Views: 3962

Answers (1)

barry houdini
barry houdini

Reputation: 46331

I'd prefer to use INDEX rather than INDIRECT, it's not volatile and it's more robust in dealing with added rows or columns than "hardcoded" values like "B" and "D", so assuming data in A1:Z100 you can use this formula for the match, assuming a search value of "x"

=MATCH(1,INDEX(B2:Z100,MATCH("x",A2:A100,0),0),0)

...and you can add an extra INDEX function to retrieve the first row value for that column

=INDEX(B1:Z1,MATCH(1,INDEX(B2:Z100,MATCH("x",A2:A100,0),0),0))

Upvotes: 1

Related Questions