Expert: Tom Ogilvy - 9/5/2008

Question
QUESTION: I have two worksheet tabs.  A (to be populated) and B (raw data).  I want to populate column A in tab A with all the corresponding matches from tab B.

Formula =INDEX(B!A:A,MATCH($C$1,(B!D:D),0))gives me the first match for $C$1 (drop down cell), but when I copy this formula down, it returns the same first value from the match in tab B.  How can I get this to 'loop' to return all the matches in tab B?

ANSWER: Dale,

Ashish Mathur has documented how to do this on the Microsoft Office support site:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx#Return%20multiple%20

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Thanks Tom,
For the most part that worked.  However, I modified the formula to use with the spreadsheet I have.  Now I am experiencing a 1 off problem.  The result returns 326 rows, when there should be 327 rows of data.  The formula I used is as follows:  

{=IF(ISERROR(INDEX(FSR!$A$2:$S$20000,SMALL(IF(FSR!$D$2:$D$20000=$C$1,ROW(FSR!$D$2:$D$20000)),ROW(326:326)),1)),"",INDEX(FSR!$A$2:$S$20000,SMALL(IF(FSR!$D$2:$D$20000=$C$1,ROW(FSR!$D$2:$D$20000)),ROW(326:326)),1))}

Do you know why it is dropping a row?

Dale

Answer
Dale,

ROW(326:326) tells it to return the 326th largest number.  It looks like you should drag it down 1 more row to get it to go to 327

(so if you started with row(1:1), you only have the formula in 326 cells.

--
Regards,
Tom Ogilvy