if and vlookup function NA error

B

burmajukebox

Joined
Aug 15, 2012
Messages
6
Reaction score
0
Dear All,

Pls advice me how to hide NA Error in my following excel formula

=IF(C7=VLOOKUP(C7,Table1,1),VLOOKUP(C7,Table1,2),"")

Regards,

burmajukbox
 
=IF(AND((-1*ISERROR(VLOOKUP(C7,Table1,1))),C7=VLOOKUP(C7,Table1,1)),VLOOKUP(C7,Table1,2),"")

I think that will do it, I haven't tried it out though. Let me know if it works for you!
 
alow said:
=IF(AND((-1*ISERROR(VLOOKUP(C7,Table1,1))),C7=VLOOKUP(C7,Table1,1)),VLOOKUP(C7,Table1,2),"")

I think that will do it, I haven't tried it out though. Let me know if it works for you!
Click to expand...

thank you for yr reply.

but NA error still exit , thanks anyway

pls advice again
 
Are you just trying to hide errors from the VLOOKUP? If so, try the following instead:

=IF(ISNA(VLOOKUP(C7,Table1,2)),"",VLOOKUP(C7,Table1,2))

What that will do is look to see if the VLOOKUP formula that you want to use, pulling data from the second column of table 1, returns a #N/A error. Then, if it is a #N/A error, it will return blank, otherwise, it will return the result of the formula.
 
alow said:
Are you just trying to hide errors from the VLOOKUP? If so, try the following instead:

=IF(ISNA(VLOOKUP(C7,Table1,2)),"",VLOOKUP(C7,Table1,2))

What that will do is look to see if the VLOOKUP formula that you want to use, pulling data from the second column of table 1, returns a #N/A error. Then, if it is a #N/A error, it will return blank, otherwise, it will return the result of the formula.
Click to expand...

Dear Alow

thank you very much, it works for me. In my old formula, table 1's column 1 is account code and column 2 is account name. when i type any account code in cell c7, it gives right answer.
but i type nothing in cell c7,NA error appear.when i type wrong account code that not including table 1,it give blank cell.

bur i have a little problem . in your formula, when even i type wrong account code,( eg. 1000-12000 instead of 1000-120 ) it give same account name.

so your formula is useful for me, but a little problem remain.


thank you
 
Last edited:
burmajukebox said:
Dear Alow

thank you very much, it works for me. In my old formula, table 1's column 1 is account code and column 2 is account name. when i type any account code in cell c7, it gives right answer.
but i type nothing in cell c7,NA error appear.when i type wrong account code that not including table 1,it give blank cell.

bur i have a little problem . in your formula, when even i type wrong account code,( eg. 1000-12000 instead of 1000-120 ) it give same account name.

so your formula is useful for me, but a little problem remain.


thank you
Click to expand...

hay dude

i found solution for my error by using following formula,

=IF(ISNA(VLOOKUP(C7,COA,2)),"",IF(C7=VLOOKUP(C7,COA,1),VLOOKUP(C7,COA,2),""))

thanks
 
Back
Top