if and vlookup function NA error

[burmajukebox]

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

 

[alow]

=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!

 

[burmajukebox]

=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!

thank you for yr reply.

but NA error still exit , thanks anyway

pls advice again

 

[alow]

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.

 

[burmajukebox]

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.

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

 

[burmajukebox]

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

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