Lookup second/third values

  • Thread starter Thread starter Bongard
  • Start date Start date
B

Bongard

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!
 
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!
Click to expand...

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)),ROW(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)
 
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)),ROW(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)
Click to expand...

sorry forgot to tell you that the formula should be fired by
control+shift+enter
 
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
 
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!
 
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.
 
Don Guillett said:
NO need to post in more than one group. Have you tried
data>filter>autofilter
Click to expand...
....

YEs, but crossposting is acceptable, which is what the OP did.
 
Ron Coderre said:
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.
Click to expand...
....

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.
 
ROWS(E$1:E1) instead of ROW()

Excellent suggestion, Harlan...thanks

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

XL2003, WinXP
 
Back
Top