return values from an array based on matching text value

[Guest]

I have a 3 column list of data. The first column is a text label (group), the
second is another text value and the third is a value. Using a function
(can't be a macro!!!!!) I want to get back ALL of the second text and third
value rows which match a specified value in the first column. Can this be
done?

Thanks,

joeb

 

[T. Valko]

The easiest way to do this is to use the Autofilter. See this for
instructions:

http://contextures.com/xlautofilter01.html

This could also be done with formulas but it depends on how much data you
have as to whether this would be a viable approach to take. 1000's of rows
of data = not a good approach!

Biff

 

[Guest]

More information:

I'm actually trying to setup a dashboard under Xcelsius. Xcelsius will stick
a value into a cell then will graph a range of cells so there is no USER
interaction at the spreadsheet. I have something similar working using
VLOOKUP where I only need one row which has a label and a value. If I could
get VLOOKUP to return ALL of the matching rows then I'd be good but it only
returns the first.

??????????

 

[T. Valko]

How many rows of data need to be searched? Roughly how many rows of data
will be returned?

Biff

 

[Guest]

30 to 40K rows but I can filter that will the SQL used to populate the sheet
but still about 3K rows.

BTW Thanks for the help!!

 

[T. Valko]

Well, I'll give you the formula and you'll have to see if the performance is
acceptable.

Let's use this sample:

...........A..........B..........C
1........G.........10.........19
2........T.........20..........15
3........C.........17.........22
4........A.........10.........15
5........T..........17.........40

F1 = lookup value

Try this array** formula:

=IF(ROWS($1:1)<=COUNTIF($A$1:$A$5,$F$1),INDEX(A$1:A$5,SMALL(IF($A$1:$A$5=$F$1,ROW(A$1:A$5)-MIN(ROW(A$1:A$5))+1),ROWS($1:1))),"")

Copy across a total of 3 cells and then down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

I'm betting that this is not what you had in mind but if you don't want to
use a filter then your choices are very limited!

Biff

 

[Guest]

Thanks but all I get is #VALUE using the formula and data setup exactly as
you mentioned although I'm not clear on CTRL, SHIFT, ENTER. The results I got
are from pasting the formula into the first column at cell A10. I also tried
T in cell F1 (by the way, are you involved in genetics based on your
selection of letters). This setup resulted in the #VALUE. Maybe if I
understood the logic of the formula I could figure out where the issue is.
I'm going to try looking at it and see if I can understand it.
I also don't understand what you meant about filtering.

Thanks again,

Joe B

 

[Guest]

WOW, it's ugly but this works:

My A column on my data sheet (data!) has the value I'm looking for. The data
comes in sorted on this column so:

Find the first occurrence of the cell I'm looking for which is on the agg!
sheet in cell C1 using:
=MATCH(C1,data!A:A,0)

Now the cell (C4) has the starting cell of the range of labels and values
I'm looking for. For the first of my two data columns I now use in the first
row

=IF(INDIRECT("data!A"&$C$4)=$C$1,INDIRECT("data!D"&$C$4),"")

In subsequent rows I use the above formula and in the E column I have a
series starting with the value 1 at row 16. The value increments by 1 to 300.
=IF(INDIRECT("data!A"&$C$4+E16)=$C$1,INDIRECT("data!D"&$C$4+E16),"")


Like I said, UGLY but it works and all the functions I use work under the
Xcelsius dashboard tool.

Thanks to biff who started me along this path of thinking!

Joe B

 

[T. Valko]

I'm not clear on CTRL, SHIFT, ENTER.

The formula I suggested is an array formula. Normally when you enter a
formula you type it in then hit the ENTER key. Array formulas are different.
When you type them in you need to use the key combination of
CTRL,SHIFT,ENTER, not just ENTER. That is, hold down both the CTRL key and
the SHIFT key then hit ENTER. When done properly Excel will place squiggly
braces { } around the formula. You can't just type these braces in. You
*must* use the key combination to produce them.

are you involved in genetics based on your selection of letters

Ah! Very observant. But, no I'm not. I did intentionally use those letters
for that reason, though!

If you found another way to do it then that's great.

Biff

 

[Guest]

BAHHHHHHH.......

I thought I did. We're trying to use Xcelsius to put together dashboards
from Excel spreadsheets but every time I turn around I run into another
shortfall of Xcelsius. It will only support CERTAIN Excel functions. Now I
can't use INDIRECT. Do you know of a way to create a cell reference using the
value in a cell? If I have a value of 25 in D1 I'd like to have A1 to have
the value of the cell at C25. This tool (Xcelsious) is VERY frustrating!!!!!!!

Joe B

 

[T. Valko]

Try this:

D1 = 25

=INDEX(C:C,D1)

Will return the value from C25.

Biff