How to write IF statement with VLOOKUP (Array)?

[horacehill]

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.

 

[Marcelo Branco]

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.

 

[horacehill]

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

 

[Marcelo Branco]

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.