PO numbers

[Jane]

Hello everybody:



I have two big spreadsheets. Please see a sample attached.



I want to use some formula to insert the po number on sheet 2 to the column C of sheet 1, according to the same invoice numbers on both sheets.



Thank you for your help!

[mvidas]

Hi Jane,

Any chance you can alter sheet2 so that 'invoice' is before 'po'? If they were switched, you could use the following to get the PO number:

=IF(ISERROR(VLOOKUP(A3,Sheet2!$A$2:$D$14,2,FALSE)),"",VLOOKUP(A3,Sheet2!$A$2:$D$14,2,FALSE))

otherwise, you'll have to use:

=IF(ISERROR(MATCH(A3,Sheet2!$B$2:$B$14,0)),"",INDEX(Sheet2!$A$2:$A$14,MATCH(A3,Sheet2!$B$2:$B$14,0)))

Matt

[mdmackillop]

or try

=SUMPRODUCT(--((Sheet2!B$2:B$14)=A3),--(Sheet2!A$2:A$14))

2 questions
How big are your spreadsheets as this could influence the method?
Do you need to retain the formula or just need the data?
Regards
MD

[Jane]

I tried the sample spreadsheet and it works. However, the big spreadsheet doesn't work. I don't know and will try torrow again. The spread sheet has abount 2000 lines

Thank you very much!

[mdmackillop]

Hi Jane,
I had some problems with your cell formats. It might be worth checking these if you have trouble.