VLOOKUP with Multiple References

**mbrady1973** · 12-06-2008, 11:15 PM

Hello,

I am trying to accomplish a task that uses the VLOOKUP function to retrieve data from another worksheet. The problem I am having is that it will only return one row of data.

In the formula below, there are multiple rows that contain the $C$4 reference.
I would love to figure out how to return all the rows that contain that
C$4$ reference. I hope this makes sense...??? Thanks!

=VLOOKUP($C$4,'Hot Zone Customers'!$C$2:T561,14,0)

**NBVC** · 12-06-2008, 11:27 PM

Try this formula:

=IF(ROWS($A$1:$A1)>COUNTIF('Hot Zone Customers'!$C$2:$C$561,$C$4),"",INDEX('Hot Zone Customers'!$C$2:$T$561,SMALL(IF('Hot Zone Customers'!$C$2:$C$561=$C$4,ROW('Hot Zone Customers'!$C$2:$C$561)-ROW('Hot Zone Customers'!$C$2)+1),14),ROWS($A$1:$A1)))

After you enter it, hold the CTRL+SHIFT keys down and then Hit ENTER... You will see { } brackets appear around the formula.

Then copy the formula down the column to retrieve multiple matches.

**mbrady1973** · 12-06-2008, 11:58 PM

I am receiving a #num! error....do you have any ideas?

**NBVC** · 12-07-2008, 12:16 AM

Did you follow this instruction?

After you enter it, hold the CTRL+SHIFT keys down and then Hit ENTER... You will see { } brackets appear around the formula.

**mbrady1973** · 12-07-2008, 12:39 AM

OK...I hate to be "thick", but it is counting the instanes of the occurrence, yet it is not returning any actual values. What it is doing is placing a #Num! error in the cell. For example, if there is 4 instaces of '12345' as my reference, it is teturning 4 instances of #NUM!.

Any idea what I might be doing wrong?

**NBVC** · 12-07-2008, 01:03 AM

Yes, you are correct.. I screwed up... I didn't have a sample to check against...I misplaced some parts of the Index function...

try this instead:

=IF(ROWS($A$1:$A1)>COUNTIF('Hot Zone Customers'!$C$2:$C$561,$C$4),"",INDEX('Hot Zone Customers'!$C$2:$T$561,SMALL(IF('Hot Zone Customers'!$C$2:$C$561=$C$4,ROW('Hot Zone Customers'!$C$2:$C$561)-ROW('Hot Zone Customers'!$C$2)+1),ROWS($A$1:$A1)),14))

**mbrady1973** · 12-07-2008, 01:21 AM

THANK a Bunch!!!!

What part of that formula do I need to change to return the contents of the neighboring cell(s)? I don't have the mental capacity to figure that out...

**NBVC** · 12-07-2008, 01:53 AM

Try ether:

=IF(ROWS($A$1:$A1)>COUNTIF('Hot Zone Customers'!$C$2:$C$561,$C$4),"",INDEX('Hot Zone Customers'!$C$2:$T$561,SMALL(IF('Hot Zone Customers'!$C$2:$C$561=$C$4,ROW('Hot Zone Customers'!$C$2:$C$561)-ROW('Hot Zone Customers'!$C$2)+1),ROWS($A$1:$A1)),COLUMN(R$1)))

or

=IF(ROWS($A$1:$A1)>COUNTIF('Hot Zone Customers'!$C$2:$C$561,$C$4),"",INDEX('Hot Zone Customers'!P$2:P$561,SMALL(IF('Hot Zone Customers'!$C$2:$C$561=$C$4,ROW('Hot Zone Customers'!$C$2:$C$561)-ROW('Hot Zone Customers'!$C$2)+1),ROWS($A$1:$A1))))

**NBVC** · 12-07-2008, 04:17 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save