Forum Discussion

CasonTheOnly's avatar
CasonTheOnly
Copper Contributor
May 21, 2019

Advanced IF(AND) Formula help needed

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, ""

 

excel
Excel on Mac
Excel Online
Formulas and Functions

4 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 21, 2019
    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.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 21, 2019

    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.

    • CasonTheOnly's avatar
      CasonTheOnly
      Copper Contributor
      May 21, 2019

      PeterBartholomew1 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!

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        May 21, 2019

        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), "")

Resources