USING IFNA WITH OTHER LOGICAL FUNCTIONS AND VLOOKUP!

%3CLINGO-SUB%20id%3D%22lingo-sub-1885229%22%20slang%3D%22en-US%22%3EUSING%20IFNA%20WITH%20OTHER%20LOGICAL%20FUNCTIONS%20AND%20VLOOKUP!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1885229%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20combine%20a%20VLOOKUP%20formula%20with%20other%20logical%20functions%2C%20so%20that%20if%20the%20look%20up%20returns%20an%20%23N%2FA%2C%20depending%20on%20the%20value%20of%20other%20cells%2C%20this%20may%20be%20correct.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20need%20to%20use%20IFNA%2C%20IF%2C%20AND%20and%20possibly%20OR%20in%20order%20to%20achieve%20my%20desired%20result%20but%20I'm%20not%20sure%20in%20what%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20anyone%20able%20to%20help%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1885229%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1885328%22%20slang%3D%22en-US%22%3ERe%3A%20USING%20IFNA%20WITH%20OTHER%20LOGICAL%20FUNCTIONS%20AND%20VLOOKUP!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1885328%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847248%22%20target%3D%22_blank%22%3E%40Seaneboy2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20ISNA()%20which%20returns%20TRUE%20or%20FALSE%20depends%20on%20if%20you%20have%26nbsp%3B%3CSPAN%3E%23N%2FA%20or%20not%2C%20and%20combine%20it%20as%20with%20any%20other%20condition%20which%20returns%20TRUE%20or%20FALSE.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1885426%22%20slang%3D%22en-US%22%3ERe%3A%20USING%20IFNA%20WITH%20OTHER%20LOGICAL%20FUNCTIONS%20AND%20VLOOKUP!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1885426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Thank%20you%20very%20much%20for%20your%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20you%20know%20in%20what%20order%20I%20would%20need%20to%20nest%20the%20functions%2C%20within%20the%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20argument%20I%20am%20trying%20to%20create%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22If%20the%20VLOOKUP%20returns%20%23N%2FA%2C%20and%20cell%20**%20isn't%20blank%2C%20then%20return%20this.%20And%20if%20the%20VLOOKUP%20returns%20%23N%2FA%2C%20and%20cell%20**%20is%20blank%2C%20then%20return%20this.%20And%20if%20the%20VLOOKUP%20returns%20%23N%2FA%2C%20then%20return%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1885429%22%20slang%3D%22en-US%22%3ERe%3A%20USING%20IFNA%20WITH%20OTHER%20LOGICAL%20FUNCTIONS%20AND%20VLOOKUP!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1885429%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847248%22%20target%3D%22_blank%22%3E%40Seaneboy2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%20ISNA(%20VLOOKUP()%20)%2C%20IF(cell%3D%22%22%2C%20this%2C%20that)%2C%20VLOOKUP()%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I'm trying to combine a VLOOKUP formula with other logical functions, so that if the look up returns an #N/A, depending on the value of other cells, this may be correct.

 

I think I need to use IFNA, IF, AND and possibly OR in order to achieve my desired result but I'm not sure in what order.

 

Is anyone able to help please?

 

Thanks

 

5 Replies

@Seaneboy2020 

You may use ISNA() which returns TRUE or FALSE depends on if you have #N/A or not, and combine it as with any other condition which returns TRUE or FALSE.

@Sergei Baklan  Thank you very much for your response.

 

Would you know in what order I would need to nest the functions, within the formula?

 

The argument I am trying to create is:

 

"If the VLOOKUP returns #N/A, and cell ** isn't blank, then return this. And if the VLOOKUP returns #N/A, and cell ** is blank, then return this. And if the VLOOKUP returns #N/A, then return this.

 

@Seaneboy2020 

Perhaps something like

=IF( ISNA( VLOOKUP() ), IF(cell="", this, that), VLOOKUP() )

@Sergei Baklan Thank you very much for your help.