Home

Advanced IF(AND) Formula help needed

%3CLINGO-SUB%20id%3D%22lingo-sub-624335%22%20slang%3D%22en-US%22%3EAdvanced%20IF(AND)%20Formula%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624335%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20all%20in%20advance%20for%20helping%20me%20with%20my%20issue!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20this%20report%2C%20I%20would%20like%20to%20add%20text%20to%20a%20new%20column%20(IF)%20the%20invoice%20number%20matches%20a%20one%20of%20the%20Invoice%20numbers%20in%20my%20list.%26nbsp%3B%20Allow%20me%20to%20clarify%20(I%20have%20attached%20a%20screenshot%20of%20my%20report).%20I%20have%20a%2010%2C000-row%20spreadsheet%20with%20about%20800%20invoice%20numbers.%20I%20then%20have%20a%20select%20few%20invoice%20numbers%20I%20would%20like%20to%20label%20with%20%22N%22%20and%20another%20select%20few%20I%20would%20like%20to%20label%20with%20%22F%22.%20If%20the%20invoice%20does%20NOT%20match%20I%20would%20like%20to%20leave%20it%20blank.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20to%20break%20it%20down%20a%20little%20bit%20further%3B%3C%2FP%3E%3CP%3E(IF)%20A%3A3%20%3D%20AF3%3AAF31%2C%20F%2C%20(IF)%20A%3A3%20%3D%20AF32%3AAF36%2CN%2C%20(IF)%20No%20Match%2C%20%22%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-624335%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-625806%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20IF(AND)%20Formula%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-625806%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342710%22%20target%3D%22_blank%22%3E%40CasonTheOnly%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20IF(%20LOOKUP(Invoice%2C%20InvoiceList)%3DInvoice%2C%20LOOKUP(Invoice%2C%20InvoiceList%2C%20CodeList)%2C%20%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ELookup%20performs%20an%20efficient%20bisection%20search%20on%20sorted%20data%20and%20returns%20the%20largest%20value%20%26lt%3B%3D%20the%20search%20value.%26nbsp%3B%20If%20the%20first%20test%20fails%2C%20the%20search%20value%20is%20not%20present.%26nbsp%3B%20If%20it%20succeeds%2C%20repeat%20the%20search%20but%20this%20time%20return%20the%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-627987%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20IF(AND)%20Formula%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-627987%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BIt%20seems%20to%20be%20giving%20me%20%22you've%20entered%20too%20many%20arguments%22%20error.%20I%20tried%20reducing%20the%20arguments%20and%20had%20no%20luck.%20Could%20you%20give%20me%20a%20little%20more%20detail%20on%20the%20formula%20using%20my%20screenshot%3F%20Sorry%20for%20making%20you%20spoon-feed%20me%20on%20this!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-629145%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20IF(AND)%20Formula%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-629145%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342710%22%20target%3D%22_blank%22%3E%40CasonTheOnly%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20you%20build%20the%20formula%20step%20by%20step.%26nbsp%3B%20Typing%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20LOOKUP(Invoice%2C%20InvoiceList)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Enext%20to%20the%20invoice%20column%20should%20return%20the%20invoice%20number%20where%20it%20exists%20in%20the%20lookup%20table%20and%20the%20previous%20number%20if%20there%20is%20a%20gap.%26nbsp%3B%20Note%20that%20'InvoiceList'%20is%20the%20first%20column%20of%20the%20lookup%20table.%20Adding%20the%20equality%20test%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20LOOKUP(Invoice%2C%20InvoiceList)%3DInvoice%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eshould%20give%20TRUE%2FFALSE%20depending%20on%20whether%20the%20invoice%20has%20been%20found%20or%20not.%26nbsp%3B%20You%20could%20test%20the%20actual%20lookup%20by%20itself%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20LOOKUP(Invoice%2C%20InvoiceList%2C%20CodeList)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3Eor%20try%20nesting%20it%20within%20the%20%3CSTRONG%3EIF(%3C%2FSTRONG%3E%20%3CEM%3Etest%3C%2FEM%3E%3CSTRONG%3E%2C%3C%2FSTRONG%3E%20%3CEM%3Ecode_to_return%3C%2FEM%3E%3CSTRONG%3E%2C%20%22%22%20)%3C%2FSTRONG%3E%20the%20give%20the%20full%20formula%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20IF(%20LOOKUP(Invoice%2C%20InvoiceList)%3DInvoice%2C%20LOOKUP(Invoice%2C%20InvoiceList%2C%20CodeList)%2C%20%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-632152%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20IF(AND)%20Formula%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-632152%22%20slang%3D%22en-US%22%3EYou%20may%20wrap%20VLOOKUP%20with%20IFNA%2C%20like%20this%3A%3CBR%20%2F%3E%3DIFNA(VLOOKUP(A3%2CInvoiceCodes%2C2%2C0)%2C%22%22)%3CBR%20%2F%3EThe%20foregoing%20formula%20assumes%20that%20the%20data%20in%20Columns%20AF%20and%20AG%20are%20named%20InvoiceCodes.%3C%2FLINGO-BODY%3E
CasonTheOnly
Occasional Contributor

Thank you all in advance for helping me with my issue!

 

For this report, I would like to add text to a new column (IF) the invoice number matches a one of the Invoice numbers in my list.  Allow me to clarify (I have attached a screenshot of my report). I have a 10,000-row spreadsheet with about 800 invoice numbers. I then have a select few invoice numbers I would like to label with "N" and another select few I would like to label with "F". If the invoice does NOT match I would like to leave it blank. 

 

So to break it down a little bit further;

(IF) A:3 = AF3:AF31, F, (IF) A:3 = AF32:AF36,N, (IF) No Match, ""

 

4 Replies

@CasonTheOnly 

= IF( LOOKUP(Invoice, InvoiceList)=Invoice, LOOKUP(Invoice, InvoiceList, CodeList), "")

Lookup performs an efficient bisection search on sorted data and returns the largest value <= the search value.  If the first test fails, the search value is not present.  If it succeeds, repeat the search but this time return the code.

@Peter Bartholomew It seems to be giving me "you've entered too many arguments" error. I tried reducing the arguments and had no luck. Could you give me a little more detail on the formula using my screenshot? Sorry for making you spoon-feed me on this! 

 

Thank you!

@CasonTheOnly 

I suggest you build the formula step by step.  Typing

= LOOKUP(Invoice, InvoiceList)

next to the invoice column should return the invoice number where it exists in the lookup table and the previous number if there is a gap.  Note that 'InvoiceList' is the first column of the lookup table. Adding the equality test

= LOOKUP(Invoice, InvoiceList)=Invoice

should give TRUE/FALSE depending on whether the invoice has been found or not.  You could test the actual lookup by itself

= LOOKUP(Invoice, InvoiceList, CodeList)

or try nesting it within the IF( test, code_to_return, "" ) the give the full formula 

= IF( LOOKUP(Invoice, InvoiceList)=Invoice, LOOKUP(Invoice, InvoiceList, CodeList), "")

You may wrap VLOOKUP with IFNA, like this:
=IFNA(VLOOKUP(A3,InvoiceCodes,2,0),"")
The foregoing formula assumes that the data in Columns AF and AG are named InvoiceCodes.