CODEWITHSUNDEEP
excel-formula
WK89
WK89

Reputation: 1

Excel: How to find first instance of number greater than 1 that corresponds to a specific category

I have an array of invoices that have three pieces of information: Invoice number, customer ID, and Purchase Quantity.

I need an excel formula that returns the invoice number of the first invoice to have an associated Purchase quantity that is greater than or equal to 1.

Index match would do it except it is not conditional, i.e. it would just show me the first instance where the purchase quantity was greater than or equal to 1 instead of the first instance for a particular customer where the quantity was greater than or equal to one.

etc.

Would really appreciate help on this.

Upvotes: 0

Views: 133

Answers (1)

Technichor
Technichor

Reputation: 66

Scott Craner's answer is probably best if you don't mind array formulas. I avoid them whenever possible but if it's not a large, widely-used spreadsheet I'm sure it's fine.

Personally, I would add a helper column that is just a TRUE/FALSE check whether each row matches your conditions. Easiest to understand and maintain and usually the best performance-wise as well.

Upvotes: 0

Related Questions