Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
thanks i will give that a try

Mike H said:
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
Click to expand...
 
Back
Top