Lookup with multiple returns
 

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

Lookup with multiple returns
 

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

Lookup with multiple returns
 

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

Lookup with multiple returns
 

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

Lookup with multiple returns
 

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

Lookup with multiple returns
 

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

Lookup with multiple returns
 

I suspect any difference in efficiency is likely negligible.

On a small range, probably, but with these types of formulas evey little bit
helps.


--
Biff
Microsoft Excel MVP


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

Lookup with multiple returns
 

If the range is large, and the expected results are numerous, it might
be better to use a different approach.

By the way, it would probably be a bit more efficient to enter COUNTIF
in a separate cell and then have the formula reference that cell.
Otherwise the calculation will take place numerous times instead of just
once.

In article ,
"T. Valko" wrote:

I suspect any difference in efficiency is likely negligible.

On a small range, probably, but with these types of formulas evey little bit
helps.


--
Biff
Microsoft Excel MVP


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

Lookup with multiple returns
 

500 rows of data...

Average of 5 calculation times.

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)-MIN(ROW(B$1:B$500))+1),ROWS(D$1:D1))

Average calc time: 0.00084

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)),ROWS(D$1:D 1))-MIN(ROW(B$1:B$500))+1

Average calc time: 0.00066

By the way, it would probably be a bit more efficient
to enter COUNTIF in a separate cell and then have
the formula reference that cell. Otherwise the calculation
will take place numerous times instead of just once.

I suspect any difference in efficiency is likely negligible. <grin

So, we're even!

--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
If the range is large, and the expected results are numerous, it might
be better to use a different approach.

By the way, it would probably be a bit more efficient to enter COUNTIF
in a separate cell and then have the formula reference that cell.
Otherwise the calculation will take place numerous times instead of just
once.

In article ,
"T. Valko" wrote:

I suspect any difference in efficiency is likely negligible.

On a small range, probably, but with these types of formulas evey little
bit
helps.


--
Biff
Microsoft Excel MVP


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

Lookup with multiple returns
 

In article ,
"T. Valko" wrote:

500 rows of data...

Average of 5 calculation times.

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)-MIN(ROW(B$1:B$500))+1),ROWS(D$1:D1))

Average calc time: 0.00084

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)),ROWS(D$1:D 1))-MIN(ROW(B$1:B$500))+1

Average calc time: 0.00066

So this basically proves my point. Here we have a relatively small
range with the difference in speed being somewhat insignificant.

I tried to do some informal testing under different circumstances. I
entered data from Row 5 through to Row 65000, ensuring that the criteria
is met at least 1000 times. Then I adjusted the ranges for both
formulas, entered the formula in a cell on the second row, and copied it
down to Row 1000.

The first formula took about 44 seconds to calculate. The second
formula took about 34 seconds. So, yes, the second formula is more
efficient but at this point is 34 seconds really acceptable?

By the way, it would probably be a bit more efficient
to enter COUNTIF in a separate cell and then have
the formula reference that cell. Otherwise the calculation
will take place numerous times instead of just once.

I suspect any difference in efficiency is likely negligible. <grin

<VBG

So, we're even!

Are you going to keep score or shall I? :-)

Lookup with multiple returns
 

Are you going to keep score or shall I? :-)

We both can. And then we'll compare the formulas we used to see which is
more efficient only to be admonished by H. G. for being so inefficient!

You can't win for losing in this business!

--
Biff
Microsoft Excel MVP


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

500 rows of data...

Average of 5 calculation times.

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)-MIN(ROW(B$1:B$500))+1),ROWS(D$1:D1))

Average calc time: 0.00084

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)),ROWS(D$1:D 1))-MIN(ROW(B$1:B$500))+1

Average calc time: 0.00066

So this basically proves my point. Here we have a relatively small
range with the difference in speed being somewhat insignificant.

I tried to do some informal testing under different circumstances. I
entered data from Row 5 through to Row 65000, ensuring that the criteria
is met at least 1000 times. Then I adjusted the ranges for both
formulas, entered the formula in a cell on the second row, and copied it
down to Row 1000.

The first formula took about 44 seconds to calculate. The second
formula took about 34 seconds. So, yes, the second formula is more
efficient but at this point is 34 seconds really acceptable?

By the way, it would probably be a bit more efficient
to enter COUNTIF in a separate cell and then have
the formula reference that cell. Otherwise the calculation
will take place numerous times instead of just once.

I suspect any difference in efficiency is likely negligible. <grin

<VBG

So, we're even!

Are you going to keep score or shall I? :-)

Lookup with multiple returns
 

is 34 seconds really acceptable?

It depends on the application.

I have files that take 10's of minutes to calculate. They're doing really
complex stuff, calculating linear regressions for calibrating xray
spectrometers.


--
Biff
Microsoft Excel MVP


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

500 rows of data...

Average of 5 calculation times.

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)-MIN(ROW(B$1:B$500))+1),ROWS(D$1:D1))

Average calc time: 0.00084

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)),ROWS(D$1:D 1))-MIN(ROW(B$1:B$500))+1

Average calc time: 0.00066

So this basically proves my point. Here we have a relatively small
range with the difference in speed being somewhat insignificant.

I tried to do some informal testing under different circumstances. I
entered data from Row 5 through to Row 65000, ensuring that the criteria
is met at least 1000 times. Then I adjusted the ranges for both
formulas, entered the formula in a cell on the second row, and copied it
down to Row 1000.

The first formula took about 44 seconds to calculate. The second
formula took about 34 seconds. So, yes, the second formula is more
efficient but at this point is 34 seconds really acceptable?

By the way, it would probably be a bit more efficient
to enter COUNTIF in a separate cell and then have
the formula reference that cell. Otherwise the calculation
will take place numerous times instead of just once.

I suspect any difference in efficiency is likely negligible. <grin

<VBG

So, we're even!

Are you going to keep score or shall I? :-)

Lookup with multiple returns
 

In article ,
"T. Valko" wrote:

Are you going to keep score or shall I? :-)

We both can. And then we'll compare the formulas we used to see which is
more efficient only to be admonished by H. G. for being so inefficient!

You can't win for losing in this business!

:-)

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