Look up and return value from another colunm

[pateln1594]

I have run into a problem. So I have 2 colunms as follows:

A B C D
1 123
2 444 9
3 666
4 888 7
5 123
6 666 3
7 123 1
8 444
9 444
10 666


So I want to look in Column A for the value 123 and Return the value 1 from B7 since its associated with 123. Since there are multiple 123's in Column A I am not able to get the correct result. I tried using Vlookup and Lookup. I am not getting how to do this. Please help!!

 

[pateln1594]

.........a...........b...........c.............d
1.....123....... ........
2.....444....... 9 ........
3.....666....... ........
4.....888....... 7 ........
5.....123....... ........
6.....666....... 3 ........
7.....123....... 1 ........
8.....444....... ........
9.....444....... ........
10...666....... ........

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

123​

[/td][td][/td][td][/td][td]

Value​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

444​

[/td][td]

9​

[/td][td][/td][td]

123​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

666​

[/td][td][/td][td][/td][td]

444​

[/td][td]

9​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

888​

[/td][td]

7​

[/td][td][/td][td]

666​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

123​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

666​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

123​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

444​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

444​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

666​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in E2 copied down
=SUMIF(A:A,D2,B:B)

Hope this helps

M.

 

[pateln1594]

welcome to mr excel forum

maybe something like this

[table="class: Grid"]
<tbody>[tr]
[td="bgcolor: #dce6f1"][/td]
[td="bgcolor: #dce6f1"]

a​

[/td]
[td="bgcolor: #dce6f1"]

b​

[/td]
[td="bgcolor: #dce6f1"]

c​

[/td]
[td="bgcolor: #dce6f1"]

d​

[/td]
[td="bgcolor: #dce6f1"]

e​

[/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

1​

[/td]
[td]

123​

[/td]
[td][/td]
[td][/td]
[td]

value​

[/td]
[td]

result​

[/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

2​

[/td]
[td]

444​

[/td]
[td]

9​

[/td]
[td][/td]
[td]

123​

[/td]
[td]

1​

[/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

3​

[/td]
[td]

666​

[/td]
[td][/td]
[td][/td]
[td]

444​

[/td]
[td]

9​

[/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

4​

[/td]
[td]

888​

[/td]
[td]

7​

[/td]
[td][/td]
[td]

666​

[/td]
[td]

3​

[/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

5​

[/td]
[td]

123​

[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

6​

[/td]
[td]

666​

[/td]
[td]

3​

[/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

7​

[/td]
[td]

123​

[/td]
[td]

1​

[/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

8​

[/td]
[td]

444​

[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

9​

[/td]
[td]

444​

[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

10​

[/td]
[td]

666​

[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td="bgcolor: #dce6f1"]

11​

[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
</tbody>[/table]


formula in e2 copied down
=sumif(a:a,d2,b:b)

hope this helps

m.

thanks bud! This did the trick