# Lookup Help

#### SonicBoomGolf

##### Active Member
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?

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Book1.xls
ABCD
1Sold toRevenue1000080
21000080\$ -\$ 1,000,000.00
31000080\$ -
41000080\$ 1,000,000.00
51000170\$ 145.00
61000196\$ 144.00
71004570\$ 187.00
81004572\$ 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.

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.

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)

vane0326 said:
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
ABCD
1SoldRevenue1000080
210000800.99
31000080-
410000801,000,000.00
510000800.99
61000196144
71004570187
81004572198
9
Sheet1

Replies
2
Views
2K
Replies
0
Views
1K
Replies
1
Views
576
Replies
1
Views
3K
Replies
3
Views
553

1,203,252
Messages
6,054,385
Members
444,721
Latest member
BAFRA77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back