0

How do I write this excel cell formula?

In Row 1 is a list of words and they each correspond to an "!" or an "X" in Row 2. Only one word is given an "!".

             [A]     [B]  [C]   [D]     [E]   [F] 
    Row 1:   This    is    a    list    of   words
    Row 2:    !      X     X     X      X     X

In a result cell, I want to display the word corresponding to the "!".

So the formula for this cell would accomplish:

    IF (Row2_value is "!") THEN (Show Corresponding Word Here)

In this example, it would produce the word "this" because that is what corresponds to "!".

|improve this question
1
=INDEX($1:$1,MATCH("!",$2:$2,0))

Match finds a value within a range of cells, in this case you're looking for the exclamation point in row 2. Index returns a specific position within a range. The range is row 1, and the returned position is provided by the Match function.

|improve this answer
  • I deleted my answer because this one is much better. The question requests an Excel solution. If you want to apply this answer to LibreOffice Calc, that does not support this type of row referencing as far as I can tell. Instead, you could just specify an arbitrarily large row range (like A1:Z1), and the formula will still work. – fixer1234 Nov 11 '14 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.