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.
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies