On Fri, 20 Apr 2012 17:00:31 +0000, LordAcoustic wrote:


Greetings all,

I have a certain situation that I need assistance with and would greatly
appreciate the help.

I have an excel document with three columns: User Name, Question #, and
Question Answer. So each row is a different question, but not everyone
answered every question. I duplicated a small part of the document on
sheet 1 of the attached document.

What I'm trying to do is on sheet 2. So basically I'd like a formula so
that N2 would say "Something," M2 would say "Something else," C3 would
say "Whatever," and so on.

I was wondering if that was possible and thank you so much for you
advice in advance.

http://dl.dropbox.com/u/1198609/Example.xlsx

Thank you.

Given your layout and formats, you may use an array formula.


This formula must be **array-entered**:

Sheet2!B2:

=IFERROR(INDEX(Question_Answer,MATCH(1,(User_Name= $A2)*(
Question=--TRIM(MID(B$1,FIND(" ",B$1),5))),0)),"")

Select B2 and fill right to U2

Select B2:U2 and fill down to B10:U10

In the above

User_Name Refers To: =OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A))
Question Refers To: =OFFSET(User_Name,0,1)
Question_Answer Refers To: =OFFSET(User_Name,0,2)


----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.