I need to lookup a value in the second sheet and return multiple results to
the first sheet in a row.
Example:

Sheet 1

A1 B1 C1
D1
20003399 Result(sheet2 B1) Result(sheet2 B2) Result(sheet2 B3)


Sheet 2

A1 B1
20003399 Dog
A2
20003399 Cat
A3
20003399 Horse


Hope this makes sense.
--
Thanks,
Pirate

Try...

B1:

=COUNTIF(Sheet2!A1:A100,A1)

C1, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=IF(COLUMNS($C1:C1)<=$B1,INDEX(Sheet2!$B$1:$B$100, SMALL(IF(Sheet2!$A$1:$A
$100=$A1,ROW(Sheet2!$A$1:$A$100)-ROW(Sheet2!$A$1)+1),COLUMNS($C1:C1))),""
)

Adjust the ranges, accordingly.

In article ,
Pirate wrote:

I need to lookup a value in the second sheet and return multiple results to
the first sheet in a row.
Example:

Sheet 1

A1 B1 C1
D1
20003399 Result(sheet2 B1) Result(sheet2 B2) Result(sheet2 B3)


Sheet 2

A1 B1
20003399 Dog
A2
20003399 Cat
A3
20003399 Horse


Hope this makes sense.

--
Domenic
http://www.xl-central.com

These types of formulas are already calculation intensive but you can make
it slightly more efficient by putting the INDEX offset outside of the SMALL
function:

SMALL(...,COLUMNS($C1:C1))-ROW(Sheet2!$A$1)+1

This way you're only calculationg a single offset instead of an array of
offsets.

--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
Try...

B1:

=COUNTIF(Sheet2!A1:A100,A1)

C1, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=IF(COLUMNS($C1:C1)<=$B1,INDEX(Sheet2!$B$1:$B$100, SMALL(IF(Sheet2!$A$1:$A
$100=$A1,ROW(Sheet2!$A$1:$A$100)-ROW(Sheet2!$A$1)+1),COLUMNS($C1:C1))),""
)

Adjust the ranges, accordingly.

In article ,
Pirate wrote:

I need to lookup a value in the second sheet and return multiple results
to
the first sheet in a row.
Example:

Sheet 1

A1 B1 C1
D1
20003399 Result(sheet2 B1) Result(sheet2 B2) Result(sheet2
B3)


Sheet 2

A1 B1
20003399 Dog
A2
20003399 Cat
A3
20003399 Horse


Hope this makes sense.

--
Domenic
http://www.xl-central.com

In article ,
"T. Valko" wrote:

These types of formulas are already calculation intensive but you can make
it slightly more efficient by putting the INDEX offset outside of the SMALL
function:

SMALL(...,COLUMNS($C1:C1))-ROW(Sheet2!$A$1)+1

This way you're only calculationg a single offset instead of an array of
offsets.

--
Biff
Microsoft Excel MVP



Unless I misunderstood, I don't think it will return the desired
results...

--
Domenic
http://www.xl-central.com

Let's assume the range is A5:A8.

A5 = A
A6 = B
A7 = X
A8 = C

=SMALL(IF(A5:A8="x",ROW(A5:A8)-MIN(ROW(A5:A8))+1),ROWS(A$1:A1))

Result = 3

=SMALL(IF(A5:A8="x",ROW(A5:A8)),ROWS(A$1:A1))-MIN(ROW(A5:A8))+1

Result = 3

Those do the exact same thing, just differently.

In the first example you're calculating an *array* of offsets to match the
positions of the indexed range. In the second example you're calculating a
single offset from the result of the SMALL function.

Sample file:

xSample.xls 21kb

http://cjoint.com/?fiiwCVMXaC


--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
In article ,
"T. Valko" wrote:

These types of formulas are already calculation intensive but you can
make
it slightly more efficient by putting the INDEX offset outside of the
SMALL
function:

SMALL(...,COLUMNS($C1:C1))-ROW(Sheet2!$A$1)+1

This way you're only calculationg a single offset instead of an array of
offsets.

--
Biff
Microsoft Excel MVP



Unless I misunderstood, I don't think it will return the desired
results...

--
Domenic
http://www.xl-central.com

In article ,
"T. Valko" wrote:

Let's assume the range is A5:A8.

A5 = A
A6 = B
A7 = X
A8 = C

=SMALL(IF(A5:A8="x",ROW(A5:A8)-MIN(ROW(A5:A8))+1),ROWS(A$1:A1))

Result = 3

=SMALL(IF(A5:A8="x",ROW(A5:A8)),ROWS(A$1:A1))-MIN(ROW(A5:A8))+1

Result = 3

Those do the exact same thing, just differently.

In the first example you're calculating an *array* of offsets to match the
positions of the indexed range. In the second example you're calculating a
single offset from the result of the SMALL function.

Sample file:

xSample.xls 21kb

http://cjoint.com/?fiiwCVMXaC


--
Biff
Microsoft Excel MVP

I haven't tested it... However, I suspect any difference in efficiency
is likely negligible.

--
Domenic
http://www.xl-central.com