vlookup with blank look up value

I have a Vlookup formula that I want to return 0 if the returned value is empty.

example: =(VLOOKUP(A6,F:G,2,0)+1)*C6

+-------+--------+-------+-----------+-------+-------+
|       |        |       |           | Name  | Value |
+-------+--------+-------+-----------+-------+-------+
| Name  | lookup | Price | New price | Bread | 1%    |
| Bread |        | 1     | 1.01      | Milk  | 2%    |
|       | #N/A   | 2     | #N/A      | Water | 3%    |
| Water | 0.03   | 0.1   | 0.103     |       |       |
+-------+--------+-------+-----------+-------+-------+

how would I do this?

jon

Posted 2014-02-27T16:15:55.737

Reputation: 11

Answers

Throw an iferror into it

=IFERROR((VLOOKUP(A6,F:G,2,0)+1)*C6, 0)

Raystafarian

Posted 2014-02-27T16:15:55.737

Reputation: 20 384

@jon if this answer solves your question, please hit the green checkmark to the left of the answer to mark the question as answered. This will allow users looking for a similar solution to see that this worked. – Raystafarian – 2014-02-28T11:47:32.957

If you explicitly want to do that when A6 is blank only then you can use this version

=IF(A6="",0,(VLOOKUP(A6,F:G,2,0)+1)*C6)

Raystafarian's suggestion will also deal with situations where A6 is not blank but does not exist in column F

barry houdini

Posted 2014-02-27T16:15:55.737

Reputation: 10 434