Lookup with multiple returns

[Pirate]

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]

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.

 

[T. Valko]

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.

 

[Domenic]

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.

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

 

[T. Valko]

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

 

[Domenic]

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

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

 

[T. Valko]

I suspect any difference in efficiency is likely negligible.

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

 

[Domenic]

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.

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

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

[/QUOTE]

 

[T. Valko]

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$11))

Average calc time: 0.00084

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)),ROWS(D$11))-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

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.



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

[/QUOTE]

 

[Domenic]

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$11))

Average calc time: 0.00084

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)),ROWS(D$11))-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?

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

So, we're even!

Are you going to keep score or shall I?

 

[T. Valko]

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!

 

[T. Valko]

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.

 

[Domenic]

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![/QUOTE]