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...
excel
Excel on Mac
Excel Online
Formulas and Functions
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), "")