May 21 2019 11:32 AM
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, ""
May 21 2019 01:29 PM
= 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.
May 21 2019 02:36 PM
@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!
May 21 2019 03:31 PM - edited May 21 2019 03:32 PM
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), "")
May 21 2019 09:11 PM