VLOOKUP AND ROW PROBLEM

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

beccy

Board Regular
Joined
Jul 22, 2004
Messages
116
have you tried incorporating the isna function to eliminate any N/As?
 

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
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
Joined
Jul 10, 2006
Messages
452
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))
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,136,310
Messages
5,675,002
Members
419,542
Latest member
shablagoo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top