SOLVED

IFS formula gives #N/A error

%3CLINGO-SUB%20id%3D%22lingo-sub-3001798%22%20slang%3D%22en-US%22%3EIFS%20formula%20gives%20%23N%2FA%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3001798%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20simple%20IFS%20formula%20in%20my%20sheet.%20Its%20job%20is%20to%20look%20for%20a%20certain%20value%20in%20a%20certain%20range%2C%20if%20found%20it%20should%20return%20a%20specified%20value.%20And%20it%20does%20it's%20job%20at%20the%20first%20logical%20test%2C%20but%20gives%20me%20an%20%23N%2FA%20error%20when%20trying%20the%20second%20one.%20Could%20anyone%20help%20me%20with%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20will%20attach%20the%20excel%20file%20for%20clarity.%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20is%20the%20formula%3A%3CBR%20%2F%3E%3DIFS(MATCH(LEFT(B3%2C4)%2C%24H%243%3A%24H%2430%2C0)%2C%24H%242%2CMATCH(LEFT(B3%2C4)%2C%24I%243%3A%24I%2418%2C0)%2C%24I%242)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3001798%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-3002038%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20formula%20gives%20%23N%2FA%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3002038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228636%22%20target%3D%22_blank%22%3E%40Ridi96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIFS(COUNTIFS(%24H%243%3A%24H%2430%2CLEFT(B3%2C4))%2C%24H%242%2CCOUNTIFS(%24I%243%3A%24I%2418%2CLEFT(B3%2C4))%2C%24I%242%2CTRUE%2C%22no%20match%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3002040%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20formula%20gives%20%23N%2FA%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3002040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228636%22%20target%3D%22_blank%22%3E%40Ridi96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(NOT(ISNA(MATCH(LEFT(B3%2C4)%2C%24H%243%3A%24H%2430%2C0)))%2C%24H%242%2CIF(NOT(ISNA(MATCH(LEFT(B3%2C4)%2C%24I%243%3A%24I%2418%2C0)))%2C%24I%242))%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I have a simple IFS formula in my sheet. Its job is to look for a certain value in a certain range, if found it should return a specified value. And it does it's job at the first logical test, but gives me an #N/A error when trying the second one. Could anyone help me with this?

I will attach the excel file for clarity.

Here is the formula:
=IFS(MATCH(LEFT(B3,4),$H$3:$H$30,0),$H$2,MATCH(LEFT(B3,4),$I$3:$I$18,0),$I$2)

3 Replies
best response confirmed by Ridi96 (New Contributor)
Solution

@Ridi96 

=IFS(COUNTIFS($H$3:$H$30,LEFT(B3,4)),$H$2,COUNTIFS($I$3:$I$18,LEFT(B3,4)),$I$2,TRUE,"no match")

@Ridi96 

=IF(NOT(ISNA(MATCH(LEFT(B3,4),$H$3:$H$30,0))),$H$2,IF(NOT(ISNA(MATCH(LEFT(B3,4),$I$3:$I$18,0))),$I$2))  

 

Is this what you are looking for?

Very helpful and insightful, thank you!