Lookup Help

[SonicBoomGolf]

I am trying to lookup a value and am having some trouble. Below is a small sample dataset to illustrate my goal.

Column A Column B

Sold To Revenue
1000080 $-
1000080 $-
1000080 $1,000,000.00
1000170 $145.00
1000196 $144.00
1004570 $187.00
1004572 $198.00

I have a unique list of numbers that have multiple rows of cost data but only one row of revenue data, much like number 1000080. My goal is to find the cell in coulmn B that has a value above 0. A VLOOKUP would work, but it returns only the first match for a given number. Instead, I need the lookup to return the first non-zero match it finds.

So using the model above, I would want $1,000,000 returned for 1000080. Any ideas?

 

[just_jon]

Book1.xls
	A	B	C	D
1	Sold to	Revenue	1000080
2	1000080	$ -	$ 1,000,000.00
3	1000080	$ -
4	1000080	$ 1,000,000.00
5	1000170	$ 145.00
6	1000196	$ 144.00
7	1004570	$ 187.00
8	1004572	$ 198.00
9
Sheet2

The above is an array formula, which requires entry via Control+Shift+Enter [CSE], not just Enter. Any direct edit of the formula will require re-entry via CSE. You can tell if the formula has been entered correctly, as it will have braces - { } - around it afterward.

 

[SonicBoomGolf]

Here is the formula I am using (with CRTL+SHIFT+ENTER), but I am getting a zero for every number I try

=MIN(IF(($A$1:$A$959=B966)*($G$1:$G$959>0),$B$1:$B$959))

Using my previous example, if the first two criteria are met (match for the given Sold-to number and greater than zero in column G) then I want the result from column B brough in. I get zeros back for every number I try. Not sure what is wrong.......

P.S. B966 is the first unique number I am researching (1000080) and column G is the revenue column. Column B is the respective district of the sold-to.

 

[vane0326]

Base on just_jon sample

You could use this formula its a non-array. Put the formula in cell C2 and just hit enter.


=LOOKUP(2,1/((A2:A8=C1)),B2:B8)

 

[just_jon]

Base on just_jon sample

You could use this formula its a non-array. Put the formula in cell C2 and just hit enter.


=LOOKUP(2,1/((A2:A8=C1)),B2:B8)

There's a problem with that --

Book1
	A	B	C	D
1	Sold	Revenue	1000080
2	1000080		0.99
3	1000080	-
4	1000080	1,000,000.00
5	1000080	0.99
6	1000196	144
7	1004570	187
8	1004572	198
9
Sheet1