return values from an array based on matching text value
 

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

return values from an array based on matching text value
 

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

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

return values from an array based on matching text value
 

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" wrote:

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

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

return values from an array based on matching text value
 

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

Biff

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

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

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

return values from an array based on matching text value
 

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" wrote:

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

Biff

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

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

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

return values from an array based on matching text value
 

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

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

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

Biff

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

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

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

return values from an array based on matching text value
 

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

"T. Valko" wrote:

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

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

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

Biff

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

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

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

return values from an array based on matching text value
 

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("dat a!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


"WFBJoeB" wrote:

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

"T. Valko" wrote:

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

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

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

Biff

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

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

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

return values from an array based on matching text value
 

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

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

"T. Valko" wrote:

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

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

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

Biff

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

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

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

return values from an array based on matching text value
 

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" wrote:

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

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

"T. Valko" wrote:

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

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

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

Biff

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

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

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

return values from an array based on matching text value
 

Try this:

D1 = 25

=INDEX(C:C,D1)

Will return the value from C25.

Biff

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

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

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

"T. Valko" wrote:

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

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

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

Biff

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

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

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