lookup multiple values from 2 different sheets

[Philangr8]

Hi,

I have data in 2 different sheets but I'm trying to do a vlookup and bring over multiple values from a 2nd sheet. They both have the same PO number.

For example there are 2 tabs in the attachment: "PO Lines" and "Invoice Lines"
I'm trying to bring the invoice number from "Invoice Lines" tab to "PO Lines" tab using the "PO Number and Line" as the look up value but there more then 1 invoice number for some of the PO Number.

[Pete_UK]

It would help if you showed what you expected to get in some of those Invoice# columns, so I can see if I'm on the right lines. I don't understand why you have some entries of 1 or 2 in column B of the Invoice Lines sheet.

Pete

[Philangr8]

ok, sorry I should have save the spreadsheet with the "PO Line" tab showing. 1 or 2 in column B is how the data came out when I exported it so it is part of the PO Number too. Please see the attachment.

[Pete_UK]

They seem the same to me, so this is what I've been working on. Put this formula in cell R2 of the Invoices sheet:

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

then copy down to the bottom of your data. Then you can put this formula in S2 of the other sheet:

=IFERROR(INDEX('Invoice Lines'!$O:$O,MATCH($B2&"_"&COLUMNS($S:S),'Invoice Lines'!$R:$R,0)),"")

and this can be copied across and down to suit.

Hope this helps.

Pete

[Philangr8]

It works great! Thank you I have been racking my brain on this!