Need to return multiple instances using lookup

**fugee7** · 10-31-2011, 03:28 PM

I'm trying to return multiple data for a lookup criteria.
I've see this example mentioned before but did not see the solution....

dog labrador
dog beagle
cat siamese
bird toucan
dog poodle

Column A has the type of animal and Column B had the breed.

On Sheet 2, I am trying to pull all the breeds for "dog". Lookup/match "dog" and find all breeds: labrador, beagle, poodle in successive rows.

How can I do this?

Thanks!

**Richard Buttrey** · 10-31-2011, 03:54 PM

Hi,

A VLOOKUP() function can only return one value. You need to use Data Filter.

Regards

**fugee7** · 10-31-2011, 05:17 PM

Yes I'm aware that vlookup can only return one value, I'm looking for a formula that is able to do that whether it is with index or some other function. Thanks.

**Richard Buttrey** · 10-31-2011, 06:13 PM

No,

Neither VLOOKUP(), INDEX(), MATCH() or any other single function or compound formula can return several results in the way you are expecting.

As advised previously you need to check out the Data Filter functionality which will do exactly what you want.

Regards

**fugee7** · 10-31-2011, 06:53 PM

Data filter will not work for what I am trying to do.
I saw this example on a different forum and someone provided the following as a solution:

=IF(COUNTIF(Sheet1!B$1:B$5,A$1)>=ROWS($1:1),INDEX(Sheet1!A$1:A$5,
SMALL(IF(Sheet1!B$1:B$5=A$1,ROW($1:$5)),ROWS($1:1))),"")

Unfortunately, it didn't work for me exactly as also said by the other user but said he figured it out. So now, I don't know the solution.

Maybe I wasn't clear, I'm not looking for one formula that will pull all 3 results. I'm looking for a formula that I can copy down that will result in the 3 values.

**Richard Buttrey** · 10-31-2011, 07:15 PM

Hi,

Data filter will work for what you are trying to do - see attached. If you don't like the AutoFilter then you can always use the Advanced Filter - or even a Pivot Table.

It's difficult to comment on a formula in some un-named forum, which you agree didn't originally work for the original user, doesn't work for you and for which the data being analysed is known to neither of us.

Regards

**fugee7** · 10-31-2011, 08:26 PM

I am familiar with the data filter, advanced filter and pivot table. This was just purely an example, just filtering for the breed is not the end of the equation. I need something much more advanced as I have much more data. I need to pull the information into another sheet so I can continue to build the worksheet that I am working on.

**rylo** · 11-01-2011, 01:20 AM

Hi

Using your example file:
Sheet1!D1: =IF(A1=Sheet2!$A$1,ROW(),"")
Copy down as required.

Sheet2!A2: =IF(ROW()-1>COUNTIF(Sheet1!A:A,Sheet2!$A$1),"",INDEX(Sheet1!B:B,SMALL(Sheet1!D:D,ROW()-1)))

Copy down as required.

This should bring back your list of matching breeds, and put in a blank for those formulas that don't bring back an item.

HTH

rylo

**Richard Buttrey** · 11-01-2011, 05:48 AM

Thanks for the negative reputation. Your original post simply said

On Sheet 2, I am trying to pull all the breeds for "dog". Lookup/match "dog" and find all breeds: labrador, beagle, poodle in successive rows.

You didn't originally mention that you were aware of Data Filter, and since my suggestion did exactly what you were asking for I don't see why it justifies a negative response. Had it been completely irrelevant, completely wrong or no use at all then maybe.

**fugee7** · 11-01-2011, 01:59 PM

Sorry. Didn't really mean it to be negative. I thanked you for your help. I meant it more as I disagree.
You're correct that I did not originally mention that I was aware of Data Filter but I did mention it later in the thread. I did mention that I was trying to pull the data into another sheet, Data Filter would not have helped with that. I also would have hoped that when you saw the formula I attached as an example that I was looking for something more advanced as I am working on something more complicated than filtering data.
To me it was no use at all. But I didn't mean to offend you. Have a great day.

Originally Posted by Richard Buttrey

Thanks for the negative reputation. Your original post simply said

You didn't originally mention that you were aware of Data Filter, and since my suggestion did exactly what you were asking for I don't see why it justifies a negative response. Had it been completely irrelevant, completely wrong or no use at all then maybe.

**fugee7** · 11-01-2011, 02:11 PM

Thanks! This worked perfectly!

Originally Posted by rylo

Hi

Using your example file:
Sheet1!D1: =IF(A1=Sheet2!$A$1,ROW(),"")
Copy down as required.

Sheet2!A2: =IF(ROW()-1>COUNTIF(Sheet1!A:A,Sheet2!$A$1),"",INDEX(Sheet1!B:B,SMALL(Sheet1!D:D,ROW()-1)))

Copy down as required.

This should bring back your list of matching breeds, and put in a blank for those formulas that don't bring back an item.

HTH

rylo