Lookup value with multiple results

[bud]

I have a 3 column list and need to pull the value from column 2
whenever there's a value in column 1 for that row. I've been using the
following formula to accomplish this:

{=IF(ISERROR(INDEX($A$1:$C$99,SMALL(IF($A$1:$B$99=ControlSheet!$H$6,ROW

($A$1:$B$99)),ROW(1:1)),2)),"",INDEX($A$1:$B$99,SMALL(IF($A$1:$B$99=ControlSheet!$H$6,ROW($A$1:$B$99)),ROW(1:1)),2))}

This works just fine, but my list is actually 9999 rows and not 99.

When I change the array from just 99 rows up to 9999 rows, I get no
values returned. I think this is because it's too big to calculate.

Can anyone help?

Thanks.

 

[CLR]

Have you considered Data > Filter > AutoFilter > and filtering on the
non-blanks in column A..........

hth
Vaya con Dios,
Chuck, CABGx3

 

[bud]

Is there a slick way to do that automatically? The search parameter
will change from time to time and I'd like the list to rearrange itself
when the parameter changes.

 

[Biff]

Which column are you testing for the criteria?......
IF($A$1:$B$99=ControlSheet!$H$6

What do you mean by "I get no values returned" ? Maybe you're just getting
blanks? I imagine when you try this on 9999 rows it's very slow to
calculate. One thing you can do is eliminate the double lookup. When ISERROR
evaluates to FALSE you're doing a double lookup so naturally that takes
twice as long.

It looks like you want to pull column B if column A = ControlSheet!$H$6:

=IF(ROW(1:1)<=COUNTIF(A$1:A$9999,ControlSheet!H$6),INDEX(B$1:B$9999,SMALL(IF(A$1:A$9999=ControlSheet!$H$6,ROW($1:$9999)),ROW(1:1))),"")

That's more efficient but it's not the "robust version" but you need to
sacrifice robustness for speed.

You can cut some more calc time off if you can live without the "error trap"
: ( If you want, you can use conditional formatting to hide the errors:
Conditional Formatting, Formula Is: =ISERROR(A1), set the font color to be
the same as the fill color)

=INDEX(B$1:B$9999,SMALL(IF(A$1:A$9999=ControlSheet!$H$6,ROW($1:$9999)),ROW(1:1)))

Both formulas are still array entered. Both of those will still be slow but
not as slow as it was!

Biff

 

[bud]

Thanks much for your help. My problem is solved!

I must admit to being a dope on this one. Row 5367 of my big data
table had an error listed. Once I removed the error, my equations
worked fine.

Sometimes it just helps to talk about it. Thanks again.