Formula

[Alistaire Green]

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

 

[Mike H]

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]

thanks i will give that a try

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