Forum Discussion

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

Formulas and Functions

PeterBartholomew1

Silver Contributor

May 21, 2019

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