excellistsearchindexingmatch

Show nth value in a list that matches specific criteria [EXCEL]

I would like to find the nth value in a list that matches specific criteria and have those values printed in separate places in separate worksheets.

More specifically I have a list of invoice numbers either beginning with a number or with PF and I want the first (or second, third etc) value beginning with PF to show on the Proforma Invoice worksheet, and the first (or second, third etc) value beginning with a number to show on the Invoice worksheet.

At the moment I have a selection of invoice numbers like so:

Invoice #

PF17-0982

43256

65342

96038

PF16-0934

10293

And I would like to print the nth value on this list matching the criteria I give, so either beginning in a number or beginning in PF, in a separate worksheet.

Initially I tried

=IF(LEFT('Top Sheet'!S5)="P",'Top Sheet'!S5,"")

which works to find the number beginning with PF but obviously only for the first cell in the list. I also have 

=IF(ISNUMBER(LEFT('Top Sheet'!S5)*1),'Top Sheet'!S5,"")

Which again works wonderfully for the first value in the list but not for much else.

So, naturally, I googled and tried using INDEX and MATCH and SEARCH functions as found here https://exceljet.net/formula/get-first-match-cell-contains but I don't want the output to be another list dependant on only one of the values in the invoice number list. However I don't mind having an intermediate list (e.g. list of TRUE and FALSE values) which will ultimately lead to me having a single output. I have this list at the moment which was done using:

=SUMPRODUCT(--ISNUMBER(SEARCH({"PF"},'Top Sheet'!S5)))>0

And that brings me here, I've tried various variations of the above techniques and failed hopelessly. I either get an error of some description or just TRUE and FALSE values. I hope this makes sense.

Solution

  • Another formula approach, with which you can enter the formula normally, is to use the AGGREGATE function to determine the row number within the array of invoices. With the SMALL operator, you can work on an array, ignore errors, and return multiple values.

    If Invoices is a named range containing your list of invoices, then:

    For either of these formulas, enter in some cell and fill down until the formula returns blanks. The ROWS... argument will increment and return the appropriate k for the SMALL function.

    Another method would be to use a simple Filter, then copy/paste the visible cells to where you want.

    Or you could use the Advanced Filter, which has a built-in method to paste the results to another location.