Lookup second/third values
 

I have a list of items in column a. Every time a value occurs I need
to return the value in column B to my sublist in another sheet. Here
is what I'm talking about - my list would look like this
A B
1 High
2 low
3 Right
1 Left
5 New
1 Old
1 Young
2 Ancient
3 Used

Lets say I wanted to display all items from column B where column A =
1. If I use a vlookup, of course it just returns the first value
(High) but I need my list on the other sheet to look like this:

High
Left
Old
Young

I hope this makes sense - does anyone have any ideas? I can't just use
a filter because I need this list to be on another sheet separate from
the data.

Thanks!

Lookup second/third values
 

On Aug 22, 4:49 pm, Bongard wrote:
I have a list of items in column a. Every time a value occurs I need
to return the value in column B to my sublist in another sheet. Here
is what I'm talking about - my list would look like this
A B
1 High
2 low
3 Right
1 Left
5 New
1 Old
1 Young
2 Ancient
3 Used

Lets say I wanted to display all items from column B where column A =
1. If I use a vlookup, of course it just returns the first value
(High) but I need my list on the other sheet to look like this:

High
Left
Old
Young

I hope this makes sense - does anyone have any ideas? I can't just use
a filter because I need this list to be on another sheet separate from
the data.

Thanks!

suppose the dat is from A1 and B1 dwaon.
in C1 type 1
and in D1 type or copy this formula
=IF(COUNTIF($A$1:$A$1000,$C$1)=ROWS($1:1),INDEX($ B$1:$B
$1000,SMALL(IF($A$1:$A$1000=$C$1,ROW($1:$1000)),RO W(1:1))),"")
copy D1 to D2,D3 etc till you get a blank.
note that reference number (1) is in c1 that is why $C$! in two
places in the formula. this is to help you modify the formula.

(the formula was devised by Frank Kable)

Lookup second/third values
 

On Aug 22, 5:03 pm, venkat1926 wrote:
On Aug 22, 4:49 pm, Bongard wrote:



I have a list of items in column a. Every time a value occurs I need
to return the value in column B to my sublist in another sheet. Here
is what I'm talking about - my list would look like this
A B
1 High
2 low
3 Right
1 Left
5 New
1 Old
1 Young
2 Ancient
3 Used

Lets say I wanted to display all items from column B where column A =
1. If I use a vlookup, of course it just returns the first value
(High) but I need my list on the other sheet to look like this:

High
Left
Old
Young

I hope this makes sense - does anyone have any ideas? I can't just use
a filter because I need this list to be on another sheet separate from
the data.

Thanks!

suppose the dat is from A1 and B1 dwaon.
in C1 type 1
and in D1 type or copy this formula
=IF(COUNTIF($A$1:$A$1000,$C$1)=ROWS($1:1),INDEX($ B$1:$B
$1000,SMALL(IF($A$1:$A$1000=$C$1,ROW($1:$1000)),RO W(1:1))),"")
copy D1 to D2,D3 etc till you get a blank.
note that reference number (1) is in c1 that is why $C$! in two
places in the formula. this is to help you modify the formula.

(the formula was devised by Frank Kable)

sorry forgot to tell you that the formula should be fired by
control+shift+enter

Lookup second/third values
 

With your posted data in A1:B9

Try this:
D1: (the Col_A item to match)

Put this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead fo just
Enter) in
E1:
=IF(ROW()COUNTIF($A$1:$A$10,$D$1),"",INDEX($B$1:$ B$10,SMALL(IF($A$1:$A$10=$D$1,ROW($A$1:$A$10)),ROW ())))

Copy E1 and paste into E2 and down as far as you need.

Adjust range references to suit your situation.

Is that something you can work with?
(Post back if you have more questions)
***********
Regards,
Ron

XL2003, WinXP


"Bongard" wrote:

I have a list of items in column a. Every time a value occurs I need
to return the value in column B to my sublist in another sheet. Here
is what I'm talking about - my list would look like this
A B
1 High
2 low
3 Right
1 Left
5 New
1 Old
1 Young
2 Ancient
3 Used

Lets say I wanted to display all items from column B where column A =
1. If I use a vlookup, of course it just returns the first value
(High) but I need my list on the other sheet to look like this:

High
Left
Old
Young

I hope this makes sense - does anyone have any ideas? I can't just use
a filter because I need this list to be on another sheet separate from
the data.

Thanks!

Lookup second/third values
 

NO need to post in more than one group. Have you tried
datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bongard" wrote in message
ups.com...
I have a list of items in column a. Every time a value occurs I need
to return the value in column B to my sublist in another sheet. Here
is what I'm talking about - my list would look like this
A B
1 High
2 low
3 Right
1 Left
5 New
1 Old
1 Young
2 Ancient
3 Used

Lets say I wanted to display all items from column B where column A =
1. If I use a vlookup, of course it just returns the first value
(High) but I need my list on the other sheet to look like this:

High
Left
Old
Young

I hope this makes sense - does anyone have any ideas? I can't just use
a filter because I need this list to be on another sheet separate from
the data.

Thanks!

Lookup second/third values
 

Don, sorry about posting to multiple groups, I was under the
impression that I was supposed to list the newsgroups rather than
going to each group individually to post like this. Also, if you look
at my post, I stated that I need the data on another sheet so
autofilter is not an option.

I did use Ron's code and it worked beautifully. I had to make a few
minor tweaks to get it to return the small() instance that I was
looking for but it helped me to learn what was going on.


Thanks for your help guys!

Lookup second/third values
 

Actually you did it correctly, it's multiposting (for instance post the same
question on 5 occasions in 5 different groups) that is frowned upon since
you can't see if someone else already answered the post in another
newsgroup. And if it is a complicated question it's not that fun to spend a
considerable time helping someone only to find out that he/she already
received an answer. While crossposting (posting once to multiple groups)
might skew search engines a bit at least I can see if you got an answer
already and wouldn't waste any time on it.
So if you feel the need to do this, keep on doing it this way.


--
Regards,

Peo Sjoblom




"Bongard" wrote in message
oups.com...
Don, sorry about posting to multiple groups, I was under the
impression that I was supposed to list the newsgroups rather than
going to each group individually to post like this. Also, if you look
at my post, I stated that I need the data on another sheet so
autofilter is not an option.

I did use Ron's code and it worked beautifully. I had to make a few
minor tweaks to get it to return the small() instance that I was
looking for but it helped me to learn what was going on.


Thanks for your help guys!

Lookup second/third values
 

"Don Guillett" wrote...
NO need to post in more than one group. Have you tried
datafilterautofilter
....

YEs, but crossposting is acceptable, which is what the OP did.

Lookup second/third values
 

"Ron Coderre" wrote...
With your posted data in A1:B9

Try this:
D1: (the Col_A item to match)

Put this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead fo just
Enter) in
E1:
=IF(ROW()COUNTIF($A$1:$A$10,$D$1),"",INDEX($B$1: $B$10,
SMALL(IF($A$1:$A$10=$D$1,ROW($A$1:$A$10)),ROW())) )
....
Adjust range references to suit your situation.
....

Not the ideal formula. ROW() as such will return the row of the cell
containing the formula containing the ROW() call. If the OP entered this
formula in E11 and below, it'd always return "". Always better to be more
specific with rows so formulas would work no matter where they're placed.

=IF(ROWS(E$1:E1)COUNTIF($A$1:$A$10,$D$1),"",INDEX ($B$1:$B$10,
SMALL(IF($A$1:$A$10=$D$1,ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))+1),
ROWS(E$1:E1))))

or

=IF(ROWS(E$1:E1)COUNTIF($A$1:$A$10,$D$1),"",INDEX ($B:$B,
SMALL(IF($A$1:$A$10=$D$1,ROW($A$1:$A$10)),ROWS(E$1 :E1))))

Former, though longer, is better because it limits its references to the
ranges containing data.

Lookup second/third values
 

ROWS(E$1:E1) instead of ROW()

Excellent suggestion, Harlan...thanks

***********
Best Regards,
Ron

XL2003, WinXP


"Harlan Grove" wrote:

"Ron Coderre" wrote...
With your posted data in A1:B9

Try this:
D1: (the Col_A item to match)

Put this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead fo just
Enter) in
E1:
=IF(ROW()COUNTIF($A$1:$A$10,$D$1),"",INDEX($B$1: $B$10,
SMALL(IF($A$1:$A$10=$D$1,ROW($A$1:$A$10)),ROW())) )
....
Adjust range references to suit your situation.
....

Not the ideal formula. ROW() as such will return the row of the cell
containing the formula containing the ROW() call. If the OP entered this
formula in E11 and below, it'd always return "". Always better to be more
specific with rows so formulas would work no matter where they're placed.

=IF(ROWS(E$1:E1)COUNTIF($A$1:$A$10,$D$1),"",INDEX ($B$1:$B$10,
SMALL(IF($A$1:$A$10=$D$1,ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))+1),
ROWS(E$1:E1))))

or

=IF(ROWS(E$1:E1)COUNTIF($A$1:$A$10,$D$1),"",INDEX ($B:$B,
SMALL(IF($A$1:$A$10=$D$1,ROW($A$1:$A$10)),ROWS(E$1 :E1))))

Former, though longer, is better because it limits its references to the
ranges containing data.