Forum Discussion
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.
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!
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), "")