Return Value from Match and Double Lookup

  • Thread starter Thread starter crux1
  • Start date Start date
C

crux1

Hello,
I have 3 rows of information in one tab (temp): Question_ID,
Respondent_ID, Answer_Value. I have a second tab (Detailed Report):
Respondent ID (row) and Question ID (column).

I would like to match the Respondent ID and Question ID in the Detailed
Report tab with the Respondent_ID and Question_ID in the temp tab,
returning the value of Answer_Value from the temp tab to the
corresponding empty cell in the Detailed Report tab.

Not all combinations in the Detailed Report tab will match in the temp
tab (there are more combinations possible in the Detailed Report Tab
than in the temp tab). I mention that only to clarify any testing that
would return an N/A or 0.

I have researched this and found the following link:
http://www.cpearson.com/excel/lookups.htm

I tried to adapt the Double Lookup with no luck.

I have attached a file similar to the one I am working with
(thanks.zip). I would greatly appreciate your help with this.

Thanks for any help you may provide,
Scott

Attachment filename: thanks.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=415049
 
Scott,

In cell C3 of "Detailed Report", use the formula (watch the line
wrapping and/or extra spaces that will be introduced by your mail
program):

=IF(SUMPRODUCT((temp!$B$2:$B$4000=C$2)*(temp!$A$2:$A$4000=$B2),ROW(tem
p!$B$2:$B$4000))<>0,INDIRECT("temp!C"&SUMPRODUCT((temp!$B$2:$B$4000=C$
2)*(temp!$A$2:$A$4000=$B2),ROW(temp!$B$2:$B$4000))),"")

Copy that formula to cells C3:AY239 and you should be good, though it
will take a loooooong time to calculate....

HTH,
Bernie
MS Excel MVP
 
Back
Top