ExcelBanter - Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Formula (https://www.excelbanter.com/excel-discussion-misc-queries/145985-formula.html)

I am creating a Spreadsheet to keep track of orders placed, and want to
create a front sheet to list all orders with the same Purchase order Number
or invoice number.

I know the Vlookup brings you a specific line but is there a formular which
can allow you to bring up all the lines whith this number in?

Thanks

Alistaire,

As you say returning the first instance is done with vlookup. Second and
subsequent instances canj be returned with a macro or using index:-

=INDEX(Sheet1!$A$3:Sheet1!$F50,SMALL(IF(Sheet1!$A$ 3:Sheet1!$F50=$A$5,ROW(Sheet1!$A$3:Sheet1!$F50)-ROW(Sheet1!$A$3)+1,ROW(Sheet1!$F50)+1),2),2)

The above checks sheet1 (change as req'd) in the leftmost column for the
value in A5 (all similar to Vlookup up to now). The last 2 tells the formula
to return the value from column 2 and the second to last tells it to return
the second instance of the criteria. Change this for th3 3rd etc.

It's and array so CTRL+ Shift+enter

"Alistaire Green" wrote:

I am creating a Spreadsheet to keep track of orders placed, and want to
create a front sheet to list all orders with the same Purchase order Number
or invoice number.

I know the Vlookup brings you a specific line but is there a formular which
can allow you to bring up all the lines whith this number in?

Thanks

thanks i will give that a try

"Mike H" wrote:

Alistaire,

As you say returning the first instance is done with vlookup. Second and
subsequent instances canj be returned with a macro or using index:-

=INDEX(Sheet1!$A$3:Sheet1!$F50,SMALL(IF(Sheet1!$A$ 3:Sheet1!$F50=$A$5,ROW(Sheet1!$A$3:Sheet1!$F50)-ROW(Sheet1!$A$3)+1,ROW(Sheet1!$F50)+1),2),2)

The above checks sheet1 (change as req'd) in the leftmost column for the
value in A5 (all similar to Vlookup up to now). The last 2 tells the formula
to return the value from column 2 and the second to last tells it to return
the second instance of the criteria. Change this for th3 3rd etc.

It's and array so CTRL+ Shift+enter

"Alistaire Green" wrote:

I am creating a Spreadsheet to keep track of orders placed, and want to
create a front sheet to list all orders with the same Purchase order Number
or invoice number.

I know the Vlookup brings you a specific line but is there a formular which
can allow you to bring up all the lines whith this number in?

Thanks