How do i get rid of the N/A in this formula

[Georgeo83]

i have 2 columns 1 to override the v lookup in the rate table but if both of them are blank i keep getting N/A

thanks in advance




=IF(J147>0,VLOOKUP(J147,'Trade RATE Table'!C:H,6,0),IF(K147>0,VLOOKUP(K147,'Trade RATE Table'!C:H,6,0)))

 

[djt76010]

You could try the IFERROR() function. If there is an error in evaluating the first argument (in this case the vlookup) it will calculate the 2nd argument instead.

=IFERROR(VLOOKUP(J147,'Trade RATE Table'!C:H,6,0),IFERROR(VLOOKUP(K147,'Trade RATE Table'!C:H,6,0),"Error")

in the place of "Error" you just have to figure out what you want the output to be if both are blank.

 

[xlnt]

Which cell contains this formula?

When I placed the formula in cell L1 and the lookup value in cell J147 or K147 it works fine; when I clear both J147 and K147, the formula returns the value "False". Are you sure it is pulling information from the correct locations?

 

[xlnt]

Not "IFERROR" - should be "ISERROR".

 

[djt76010]

Different function, ISERROR returns a TRUE or FALSE value telling you if there is an error. IFERROR allows you to have a backup action if your function encounters an error.

Not "IFERROR" - should be "ISERROR".

 

[Georgeo83]

hi guys thank you for your reply
this formula is in L147 the k147 cell contains this formula =IF(ISNA(VLOOKUP(D147,'SAP EMP INFO'!D:J,7,0)),"-",VLOOKUP(D147,'SAP EMP INFO'!D:J,7,0)), you are right if i hAVE J and K 147 blank it will return as false

 

[Aladin Akyurek]

i have 2 columns 1 to override the v lookup in the rate table but if both of them are blank i keep getting N/A

thanks in advance




=IF(J147>0,VLOOKUP(J147,'Trade RATE Table'!C:H,6,0),IF(K147>0,VLOOKUP(K147,'Trade RATE Table'!C:H,6,0)))

=IF(OR(N(J147),N(K147)),VLOOKUP(IF(N(J147),J147,K147),'Trade RATE Table'!C:H,6,0),"")

This can still yield #N/A if the lookup value is not available. If you are
on 2007 or later...

=IFERROR(VLOOKUP(IF(N(J147),J147,K147),'Trade RATE Table'!C:H,6,0),"")

 

[Georgeo83]

You got her Aladin works like a charm thank you very much

 

[xlnt]

Is IFERROR new to 2007 and later? I hadn't heard of it before. I am (tragically and embarassingly) still working with 2003.