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