How to avoid the occurance of unexpected VLOOKUP #N/A errors

**luannc** · 03-15-2011, 03:08 AM

Hi,

Could you please suggest How can i avoid the occurance of unexpected VLOOKUP #N/A errror.

How can i use ISERROR , ISNA to avoid the #N/A option .

Kindly suggest how can i use ISERROR / ISNA in the below mentioned formula ,

=+VLOOKUP(C25,Sum_Expl!$B$3:$D$106,2,FALSE)*D25

**pike** · 03-15-2011, 03:22 AM

Hi luannc,
you can check for a match first

=if(isnumber(Match(c25,Sum_Expl!$B$3:$B$106,0)),+VLOOKUP(C25,Sum_Expl!$B$3:$D$106,2,FALSE)*D25,"Not Found")

**arthurbr** · 03-15-2011, 07:43 AM

An alternative perhaps

Please Login or Register  to view this content.

Edit/Delete Message

**luannc** · 03-15-2011, 08:36 AM

Hi
pike/Arthur ,

i used your suggested formula but the result says notfound,

I used this the below mentioned formula . it helped me to remove the N/A but it is not picking up the value .

=IF(ISNA(+VLOOKUP(C34,Sum_Expl!$B$3:$D$106,2,FALSE)),"",VLOOKUP(C34,Sum_Expl!$B$3:$D$106,2,FALSE)*D34)

kindly suggest

Can i sent the attachment to any other id , I am unable to upload the file . it is interlinked file (6.3mb ) ,if you can see the file may be you will be able to give better suggestion .

Actually it is a varience file . It help us show the varience of a particular product . As we have added new menu to our restaurant I wanted to make changes in the file but the file is not expecting the changes .

There are around 10 sheet which are interlinked with each other . If I want to make a small change, like changing the number , the file is not accepting .

kindly suggest

**arthurbr** · 03-15-2011, 10:27 AM

the "+" sign before your first Vlookup function is not necessary.
Maybe post a small sample stripped from confidential information

**luannc** · 03-16-2011, 04:11 AM

Hi
Arthur ,

I Managed to upload a file , Though the file was initially 6.3mb , i have deleted few sheets to bring it down to 3.7mb . the file will show REF# as the interlinked file is missing .

My concern was , Actually the sheet which i have attached does not acccept any changes . Even if i retype the same number it will show as N/A in other sheets .

For EG:- I entered new products in master menu (spicy paneer , Spicy chicken (blue ) ) but the sum _bom Sheet (cells shaded in yellow) shows N/A . But after i used ISNA the NA has vanished but it is not showing the value .

if you check the promix sheet , it will aslo have N/A ,

How do i get rid of the N/A . please
Kindly assist

**pike** · 03-16-2011, 06:25 AM

Hi luannc
which cell and worksheet?
maybe

Please Login or Register  to view this content.

**luannc** · 03-16-2011, 12:17 PM

hi pike ,

sum_bom sheet , cell no e37, f37 ,g 37. (cell shaded in yellow )
I hv added the isNa formula, but the cell is not picking up the value .

**tom.hogan** · 03-16-2011, 02:16 PM

Your attached workbook is a mess due to the removed sheets generating multiple #ref errors. That being said, I believe if you just remove the + sign from in front of the vlookup command your problem will be fixed.

Regards,

Tom