Formula
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
|