Re: Want Vlookup to list multiple items with the same key?

Hi!

> Currently a table of data is transfered to a *** using
> (INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$20=$C$2,ROW($1:$20)),ROW(1:1))

As written that formula will not work properly. You'll either get errors or
possibly incorrect results.

The size of the array that is indexed: sheet1!$C$2:$C$20

Must be the same size as: ROW($1:$20)

sheet1!$C$2:$C$20 = 19
ROW($1:$20) = 20

The "least" confusing way to make sure these arrays are the same size is to
use the same sized range reference in the ROW function as you do the INDEX
and then subtract the offset:

ROW(C$2:C$20)-ROW(C$2)+1

The purpose of the expression: ROW(C$2:C$20)-ROW(C$2)+1

is to return an array that is the same size as the indexed array

So:

sheet1!$C$2:$C$20 = 19 (1:19)
ROW(C$2:C$20)-ROW(C$2)+1 = 19 (1:19)

Ok, now, as far as your latest question goes, I'm not following you!!!!

I don't understand what you're trying to do!

Biff

"GarToms" <GarToms.21ufer@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:GarToms.21ufer@xxxxxxxxxxxxxxxxxxxxxxx
>
> Biff and all,
>
> I wonder if you could assist me further with something using the
> formula you suggested.
>
> Currently a table of data is transfered to a *** using
> (INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$20=$C$2,ROW($1:$20)),ROW(1:1))
>
> The rows search the source data in *** 1 by a key (column A) and
> display a name (column B), a value (column C) and a digit in column D
> decides under where the value (column C) will go. The column C value
> needs to go under column E, F, G, H, I, and to make it more complecated
> these column headings are formulas and change when the data is updated.
>
> The table transfers all the data but i cannot make it sort the value in
> column C to be in the correct column in my table.
>
> Anyone have any ideas? I was thinking there may be an if statement or
> something i could use.
>
> I would be very great full for any assistance with this.
>
>
> --
> GarToms


.