vlookups error message

[wmjenner]

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

Thanks,
Bil

 

[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]

I'd be very interested in a different approach. What I want to do i
look up all the dollar amounts associated with a specific accoun
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]

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]

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

Bil

 

[Biff]

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

Biff