Lookup_finding all the corresponding cell...

[Aline]

The formula on the E1 is =LOOKUP(2,1/(A1:A20=D1),B1:B20), but it only shows
the last cell (on the B column) of the same item on the E column


A B C D E
BIRD 8 BIRD
BIRD CAT
BIRD COW 4
CAT DOG
CAT 9 HORSE 7
CAT
COW
COW
COW 4
DOG 3
DOG
HORSE 7

How can I rewrite the formula that will :

A B C D E
BIRD 8 BIRD 8
BIRD CAT 9
BIRD COW 4
CAT DOG 3
CAT 9 HORSE 7
CAT
COW
COW
COW 4
DOG 3
DOG
HORSE 7
HORSE

Thanks,
Aline

 

[Max]

How about a simple SUMIF?
In E1, copied down: =SUMIF(A:A,D1,B:B)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Aline]

Thanks Max,

It works.

One more question, how can I make it work if on B column is not number
(8,9...) but such as Yes (or No) instead.

 

[Max]

Thanks Max,
It works.

Swell. Do take a moment to press the "Yes" button in that response (like the
ones below).

One more question, how can I make it work if on B column is not number
(8,9...) but such as Yes (or No) instead.

Paste into E1's formula bar, then array-enter** the formula below, ie press
CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing ENTER:
=INDEX(B$1:B$12,MATCH(1,(A$1:A$12=D1)*(B$1:B$12<>""),0))
Copy E1 down. Adapt the ranges to suit.

**If you did the CTRL+SHIFT+ENTER confirmation (the "array-enter") properly,
you should see Excel wrap curly braces around the formula in the formula bar,
viz. it'll look like this:
{=INDEX(B$1:B$12,MATCH(1,(A$1:A$12=D1)*(B$1:B$12<>""),0))}

If you don't see the curlies, just re-click inside the formula bar,
re-do the CTRL+SHIFT+ENTER confirmation
Then look again that the curlies are there

If the formula is not array-entered, it will not return the correct result.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---

 

[Aline]

Wow, thanks for your hard work. It looks so complicated. To be honest, I
don’t feel comfortable with the formula that you provided
(=INDEX(B$1:B$12,MATCH(1,(A$1:A$12=D1)*(B$1:B$12<>""),0))
Just wondering if it's possible to modify the formula I had previously.

=LOOKUP(2,1/(A1:A20=D1),B1:B20)

Thanks,
Aline

 

[Max]

Wow, thanks for your hard work.

No prob. But I'd request that you take a moment to press that "Yes" button
below. You haven't done so, as yet.

It looks so complicated. To be honest,

I don’t feel comfortable with the formula that you provided:

=INDEX(B$1:B$12,MATCH(1,(A$1:A$12=D1)*(B$1:B$12<>""),0))

It's not really complicated. Just an array formula, which I did stress the
importance of correctly confirming it, so that it'll function properly. Why
don't you give it a try, instead of dismissing it? It'll return you the exact
results that you seek. Remember the hard work that I did in coming up with it
specially for you? All will go down the drain if you don't even want try it.

Just wondering if it's possible to modify the formula I had previously.
=LOOKUP(2,1/(A1:A20=D1),B1:B20)

Not possible. Each formula is designed to serve a certain purpose.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---