Using an array formula to return multiple look up results from a different worksheet

[stupope]

I am somewhat familiar with Excel, but this one has me stumped. I am trying list in a column a set of results from a looked up value. The results are on a different worksheet than what I'm trying to display on. Here is the formula I"m using.

=INDEX($U$4:$V$8, SMALL(IF(($C$4=$U$4:$U$8), ROW($U$4:$U$8)-MIN(ROW($U$4:$U$8))+1, ""),ROWS($A$1:A1)), 2)

In this formula the table I'm pulling from U4 through V8 is on the same Worksheet. I need to pull from a different worksheet. If this has been tackled please direct me to it.

 

[James006]

Hi,

If we assume your data is located in the Sheet Data ...

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$A$2:$A$21,$B$1),INDEX(Data!$B$2:$B$21,SMALL(IF(Data!$A$2:$A$21=$B$1,ROW(Data!$A$2:$A$21)-ROW(Data!$A$2)+1),ROWS(B$2:B2))),"")

Hope this will help

 

[stupope]

Hi,

If we assume your data is located in the Sheet Data ...

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$A$2:$A$21,$B$1),INDEX(Data!$B$2:$B$21,SMALL(IF(Data!$A$2:$A$21=$B$1,ROW(Data!$A$2:$A$21)-ROW(Data!$A$2)+1),ROWS(B$2:B2))),"")

Hope this will help

James, I don't need a count of the cells, I need to return a list of matches. Or maybe i'm reading your formula wrong? Think of a Vlookup with multiple values. I want to return the all the possible matched values in a column. Is there a way to post an image on here? I can get my formula to work if my data is in the same sheet. I need to return it from a different sheet in the same workbook. Here is a link to the formula I'm using.

I used this guys formula for "Return multiple values vertically" but his formula does not allow me to select the data table on a different sheet. Thanks!

https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

 

[Fluff]

Just add the sheet name to the formula like
INDEX(Sheet1!$U$4:$V$8

 

[James006]

Feel free to be more precise ...

1. In which Sheet is your Formula located ...

2. In which Sheet is your Data located ...