• 544K Members
• 4,255 Online
• 649K Conversations

## Advanced IF(AND) Formula help needed

Highlighted
Occasional Contributor

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

4 Replies
Highlighted

# Re: Advanced IF(AND) Formula help needed

= 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.

Highlighted

# Re: Advanced IF(AND) Formula help needed

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

Highlighted

# Re: Advanced IF(AND) Formula help needed

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

Highlighted

# Re: Advanced IF(AND) Formula help needed

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.