# VLOOKUP Problem

#### Henry G.

##### New Member
In cell B2 have written the formula =VLOOKUP(A1,TABLE1,2,FALSE)

When there is no match for cell A1 in the first column of “table1”, the formula yields “#N/A”. I want to write a formula in cell B2 that will yield zero when no match is found and the value of column 2 in “table1” when there is a match.
THANKS . . .
Henry G.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

##### MrExcel MVP
On 2002-10-21 09:57, Henry G. wrote:
In cell B2 have written the formula =VLOOKUP(A1,TABLE1,2,FALSE)

When there is no match for cell A1 in the first column of “table1”, the formula yields “#N/A”. I want to write a formula in cell B2 that will yield zero when no match is found and the value of column 2 in “table1” when there is a match.
THANKS . . .
Henry G.

Two fast options...

[1]

=IF(ISNA(VLOOKUP(A1,TABLE1,2,0))),0,GETV())

http://longre.free.fr/english/index.html

[2]

=IF(ISNUMBER(MATCH(A1,INDEX(TABLE1,0,1),0)),VLOOKUP(A1,TABLE1,2,0),0)

#### jimboy

##### Well-known Member
try this;

=IF(ISERROR(VLOOKUP(A1,table,1,0)),0,VLOOKUP(A1,table,2,0))

#### Mark W.

##### MrExcel MVP
On 2002-10-21 09:57, Henry G. wrote:
In cell B2 have written the formula =VLOOKUP(A1,TABLE1,2,FALSE)

When there is no match for cell A1 in the first column of “table1”, the formula yields “#N/A”. I want to write a formula in cell B2 that will yield zero when no match is found and the value of column 2 in “table1” when there is a match.
THANKS . . .
Henry G.

Why not add the needed records to your table to prevent the return of #N/A, or use an IF function to test the lookup_value and return 0 rather then performing a VLOOKUP?

Replies
5
Views
342
Replies
14
Views
507
Replies
10
Views
493
Replies
1
Views
465
Replies
3
Views
270

1,181,461
Messages
5,930,071
Members
436,718
Latest member
messa57fr

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