# VLOOKUP AND ROW PROBLEM

#### mrbeanyuk

##### Board Regular
I have three master/summary spreadsheets which are linked in several ways to about 40 others. I have three because it is too much info to put in one. One linking is via VLOOKUP.

The problem I have is that a partilcar row has the VLOOKUP data in but if the result is zero it returns a #N/A across the whole row with the exception of one or two cells which may have data. I would like to add to my forumla that if the row has a figure in any cell of that row, all #N/As go blank in that row.

Is this achievable?

Thanks and help!

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### beccy

##### Board Regular
have you tried incorporating the isna function to eliminate any N/As?

#### mrbeanyuk

##### Board Regular
isna function? Will this get rid of all the n/a's as I only want this to happen on several rows and leave N/A's in some

Thanks

#### bsnapool

##### Active Member
you could also try the iserror function, - if there is any error this will bring back a blank or something you choose....

|Example)

Code:
``=IF(ISERROR(VLOOKUP(E3,\$B\$3:\$C\$11,2,FALSE)),"",VLOOKUP(E3,\$B\$3:\$C\$11,2,FALSE))``

#### mrbeanyuk

##### Board Regular
WORKS A TREAT! Thank you!

#### pgc01

##### MrExcel MVP
Hi all

Using ISERROR in this case should be avoided. It can lead to overlooking other type of errors. Use ISNA instead.

Using bsnapool's example

=IF(ISNA(VLOOKUP(E3,\$B\$3:\$C\$11,2,FALSE)),"",VLOOKUP(E3,\$B\$3:\$C\$11,2,FALSE))

ISNA with VLOOKUP tells you if the value was not found. In that case you write the empty string.

Now suppose this formula is in A10 and you copy it up until A1. You will notice that in A1:A7 you have the error #REF! which tells you that there is something wrong in that formula, in this case you are off the worksheet. You can then review your logic and correct the formula.

If you had used ISERROR you would not be warned about the error (that has nothing to do with VLOOKUP, it's an invalid address error).

Unsing ISERROR instead of ISNA may lead to having invalid formulas in the worksheet without knowing.

Hope this helps
PGC

Replies
1
Views
612
Replies
8
Views
300
Replies
2
Views
125
Replies
11
Views
1K
Replies
2
Views
585

1,172,173
Messages
5,879,467
Members
433,435
Latest member
bryanrobson

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