# How to write IF statement with VLOOKUP (Array)?

#### horacehill

##### New Member
The formula array in D1 is:
 {=VLOOKUP(B1,IF(C1=Sheet1!\$C\$4:\$C\$5,Sheet1!\$B\$4:\$D\$5),3,0)}

<colgroup><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

-- removed inline image ---

A B C D
1 MDS Company 165EB 33,091
2
3
4 ABC Company 9AC25 148,238
5 MDS Company 165EB 33,0091
6
7
If I blank out (erase text) in B5, I will get a #N/A in D1. Instead of #N/A in D1, I would like to see 0 (zero). How to write a IF statement in D1 to get 0 (zero)? Please help. Thanks.

Last edited:

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
if you are using Excel 2007 or higher try

=IFERROR(VLOOKUP(B1,IF(C1=Sheet1!\$C\$4:\$C\$5,Sheet1!\$B\$4:\$D\$5),3,0),0)

Ctrl+Shift+Enter

M.

It worked! What a coincidence...you helped solve a formula problem a few months ago. Thanks again.

It worked! What a coincidence...you helped solve a formula problem a few months ago. Thanks again.

You are welcome and thanks for the feedback.

M.

Replies
17
Views
252
Replies
1
Views
365
Replies
3
Views
129
Replies
7
Views
287
Replies
5
Views
102

1,202,914
Messages
6,052,534
Members
444,590
Latest member
GCLee

### 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