INDEX / MATCH / VLOOKUP - returning values from worksheets based upon matching cells

Morning all

Wonder if you can help with this, it's similar to something I've done before (got the solution from here) but doesnt seem to work with this data.

See the attached file (TEST.xls) I am looking match the "PO Number" from sheet 1 with the "Cust Ref" from sheet 2. If there is a match, I would like to populate sheet 5 with the appropriate "Req-Ref" from sheet 2

Hope this makes sense, thanks in advance

Phil

perfect! thanks a lot

The attached file shows how this can be done. I've put this formula in G2 of sheet 1:

=IF(ISNA(MATCH(D2,'2'!C:C,0)),"",MAX(G$1:G1)+1)

and when this is copied down it gives a sequence where matches are found in column C of sheet 2. This formula is in A2 of Sheet5:

=IFERROR(INDEX('1'!D:D,MATCH(ROWS(A$1:A1),'1'!G:G,0)),"")

and lists the matching PO numbers all bunched up. This one in B2:

=IF($A2="","",INDEX('2'!B:B,MATCH($A2,'2'!C:C,0)))

then gives you the corresponding data from sheet 2. I've copied both these down to row 20, though you could copy them further if you need to.

Hope this helps.

Pete