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.

Just a quick update... I've tried to use =index(match) functions, but I don't think that is working... I've also tried some =if(AND) functions and that isn't working either.

This worked... as posted here by StephenR...http://www.ozgrid.com/forum/showthread.php?t=164607

=IFERROR(INDEX(Sheet1!$C$2:$C$33,MATCH(1,IF(Sheet1 !$A$2:$A$33=$A2,IF(Sheet1!$B$2:$B$33=VALUE(SUBSTIT UTE(B$1,"Question ","")),1)),0)),"")

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.