can offset/match do this?

  • Thread starter Thread starter wags
  • Start date Start date
W

wags

I can not make this work. I have one sheet with 2 columns
of names and colors in sample below. I can count the
number of green & blue. How can I get the names
associated with green from the Source Sheet to display on
Report Sheet in same order. Column H will always contain
list of names that can change. I named range g1:h6
Current.
I can get the =VLOOKUP(A2,Current,2,FALSE)to give me 1st
name, but now I am stuck. Keep getting errors. Read
several nice conversations on offset/match but I can not
get it to work on text.

Source Sheet
g h
1 green sally
2 green sam
3 green jean
4 blue sam
5 blue joe
6 red allen

Report Sheet
a b
1 color is # people is
2 green 3
3
4 sally
5 sam
6 jean

Thanks for any help.
wags
 
wags said:
I can not make this work. I have one sheet with 2 columns
of names and colors in sample below. I can count the
number of green & blue. How can I get the names
associated with green from the Source Sheet to display on
Report Sheet in same order. Column H will always contain
list of names that can change. I named range g1:h6
Current.
I can get the =VLOOKUP(A2,Current,2,FALSE)to give me 1st
name, but now I am stuck. Keep getting errors. Read
several nice conversations on offset/match but I can not
get it to work on text.

Source Sheet
g h
1 green sally
2 green sam
3 green jean
4 blue sam
5 blue joe
6 red allen

Report Sheet
a b
1 color is # people is
2 green 3
3
4 sally
5 sam
6 jean

Thanks for any help.
wags
Click to expand...

Take a look here in the paragraph "Arbitrary Lookups" for a method of
returning the 2nd, 3rd, etc. occurrences:
http://www.cpearson.com/excel/lookups.htm
 
I can make example on cpearson.com work but can't
understand how to transulate that to my problem. Don't
believe I need to count how many names on changing list.
I need to reproduce the list on 2nd spreadsheet determined
by the category they fall into.
Plz HELP
wags
 
Back
Top