Lookup Value That Greater Than Zero

[fireboltpk]

Hi,

I have a data set with 600 products where I need to pickup the value that is greater than zero. Below is an example of my data set and what I need to pick.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Coke[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Coke[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Coke[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Coke[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Fanta[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Fanta[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Fanta[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

What I basically want is that when I do a lookup for Coke and Fanta, my return values are 7 and 5.

Regards,

M

 

[steve the fish]

Heres one way.

=LOOKUP(2,1/(($A$2:$A$8=E1)*($B$2:$B$8>0)),$B$2:$B$8)

Place 'Coke' or 'Fanta' into E1 and your table in A1:B8.

 

[bosco_yip]

Or………..

=1/LOOKUP(9^9,1/B$2:B$8/(A$2:A$8=E1))

Regards
Bosco

 

[Peter_SSs]

Other possible options.
F1 if you have Office 365
G1 for all versions but this is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	Product	Price			Coke	7	7
2	Coke	0			Fanta	5	5
3	Coke	0
4	Coke	0
5	Coke	7
6	Fanta	0
7	Fanta	5
8	Fanta	0
Max

 

[RoryA]

If you only have one value > 0 for each product, you could use:

=SUMIF(A:A,"Coke",B:B)

for example.

 

[fireboltpk]

Works perfectly. Thank you so much

 

[Peter_SSs]

Works perfectly. Thank you so much

Lots of options.
Thanks for letting us know.