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

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" wrote in
message ...

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

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

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" wrote in message
...

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" wrote
in
message ...

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

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

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

Biff

"wmjenner" wrote in
message ...

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