vlookups error message

[wmjenner]

I am trying to use Alan Beban's famous vlookups formula to find multiple occurences within an array. I have copied the formula down to accomodate all possible occurrences of the lookup criterion, yet I keep getting the error message "Select at least (x) rows" wherever the looked up value has more than a single occurrence. I'm not sure what "rows" are being referred to in this message since, as I said, I've copied down enough to cover the maximum potential answers. Anyone familiar with this?

Thanks,
Bill

[Biff]

Hi!

I've never used Alan's add-in but I've seen the Vlookups function discussed
here.

I think you have to select a range of cells as an array and then enter the
formula rather than entering the formula in a single cell and copying. I may
be wrong but I'd try that.

Want an alternative solution using native functions that doesn't require an
add-in? This is fairly easy to do using native functions. If so, just
provide the details.

Biff

"wmjenner" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to use Alan Beban's famous vlookups formula to find multiple
> occurences within an array. I have copied the formula down to accomodate
> all possible occurrences of the lookup criterion, yet I keep getting the
> error message "Select at least (x) rows" wherever the looked up value
> has more than a single occurrence. I'm not sure what "rows" are being
> referred to in this message since, as I said, I've copied down enough
> to cover the maximum potential answers. Anyone familiar with this?
>
> Thanks,
> Bill
>
>
> --
> wmjenner
>
>
> ------------------------------------------------------------------------
> wmjenner's Profile:
> http://www.excelforum.com/member.php...fo&userid=5282
> View this thread: http://www.excelforum.com/showthread...hreadid=550661
>

[wmjenner]

I'd be very interested in a different approach. What I want to do is
look up all the dollar amounts associated with a specific account
number and then list them. For example:

Acct.No. Amount
123 $5.00
456 $10.00
789 $15.00
123 $20.00
123 $25.00

If I want to look up by acct. no. 123, the result should be

$5.00
$20.00
$25.00

Thanks for offering to help!

Bill

Biff Wrote:
> Hi!
>
> I've never used Alan's add-in but I've seen the Vlookups function
> discussed
> here.
>
> I think you have to select a range of cells as an array and then enter
> the
> formula rather than entering the formula in a single cell and copying.
> I may
> be wrong but I'd try that.
>
> Want an alternative solution using native functions that doesn't
> require an
> add-in? This is fairly easy to do using native functions. If so, just
> provide the details.
>
> Biff
>
> "wmjenner" [email protected] wrote
> in
> message news:[email protected]...
>
> I am trying to use Alan Beban's famous vlookups formula to find
> multiple
> occurences within an array. I have copied the formula down to
> accomodate
> all possible occurrences of the lookup criterion, yet I keep getting
> the
> error message "Select at least (x) rows" wherever the looked up value
> has more than a single occurrence. I'm not sure what "rows" are being
> referred to in this message since, as I said, I've copied down enough
> to cover the maximum potential answers. Anyone familiar with this?
>
> Thanks,
> Bill
>
>
> --
> wmjenner
>
>
>
> ------------------------------------------------------------------------
> wmjenner's Profile:
> http://www.excelforum.com/member.php...fo&userid=5282
> View this thread:
> http://www.excelforum.com/showthread...hreadid=550661
>


--
wmjenner

[Biff]

Here's a small sample file: 16kb

http://cjoint.com/?gldXzR6oLA

The formula in column B needs to be copied down to enough cells to return
all the possible matches. I have colored the cells where I copied the
formula to. Make a selection from the drop down and see what happens.

Biff

"wmjenner" <[email protected]> wrote in message
news:[email protected]...
>
> I'd be very interested in a different approach. What I want to do is
> look up all the dollar amounts associated with a specific account
> number and then list them. For example:
>
> Acct.No. Amount
> 123 $5.00
> 456 $10.00
> 789 $15.00
> 123 $20.00
> 123 $25.00
>
> If I want to look up by acct. no. 123, the result should be
>
> $5.00
> $20.00
> $25.00
>
> Thanks for offering to help!
>
> Bill
>
> Biff Wrote:
>> Hi!
>>
>> I've never used Alan's add-in but I've seen the Vlookups function
>> discussed
>> here.
>>
>> I think you have to select a range of cells as an array and then enter
>> the
>> formula rather than entering the formula in a single cell and copying.
>> I may
>> be wrong but I'd try that.
>>
>> Want an alternative solution using native functions that doesn't
>> require an
>> add-in? This is fairly easy to do using native functions. If so, just
>> provide the details.
>>
>> Biff
>>
>> "wmjenner" [email protected] wrote
>> in
>> message news:[email protected]...
>>
>> I am trying to use Alan Beban's famous vlookups formula to find
>> multiple
>> occurences within an array. I have copied the formula down to
>> accomodate
>> all possible occurrences of the lookup criterion, yet I keep getting
>> the
>> error message "Select at least (x) rows" wherever the looked up value
>> has more than a single occurrence. I'm not sure what "rows" are being
>> referred to in this message since, as I said, I've copied down enough
>> to cover the maximum potential answers. Anyone familiar with this?
>>
>> Thanks,
>> Bill
>>
>>
>> --
>> wmjenner
>>
>>
>>
>> ------------------------------------------------------------------------
>> wmjenner's Profile:
>> http://www.excelforum.com/member.php...fo&userid=5282
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=550661
>>
>
>
> --
> wmjenner

[wmjenner]

Thanks a lot, Biff. I will study this tomorrow but just looking at it, it looks like a great solution. Thanks again!

Bill

[Biff]

It does the same thing as Alan's Vlookups UDF but doesn't require the
"overhead" of an add-in.

Biff

"wmjenner" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks a lot, Biff. I will study this tomorrow but just looking at it,
> it looks like a great solution. Thanks again!
>
> Bill
>
>
> --
> wmjenner
>
>
> ------------------------------------------------------------------------
> wmjenner's Profile:
> http://www.excelforum.com/member.php...fo&userid=5282
> View this thread: http://www.excelforum.com/showthread...hreadid=550661
>