Return value from multiple sheet index match?

[Melbs_CJW]

Hi all,

Apologies if this is a simple one to answer - my formulas are all breaking down to the point I'm wondering if I'm approaching it the right way.

I have a (manually updated) file tracking shipments and need to identify if these are on track by comparing against an automatically generated excel file I export each week.

I want to be able to return the value of 'shipment status' after checking for the purchase order across 3 different worksheets.

	Book1 <tbody> </tbody>
	Sheet1 <tbody> </tbody>			Sheet2 <tbody> </tbody>			Sheet3 <tbody> </tbody>
	G <tbody> </tbody>	H		G <tbody> </tbody>	H		G <tbody> </tbody>	H
1	Purchase Order <tbody> </tbody>	Shipment Status		Purchase Order <tbody> </tbody>	Shipment Status		Purchase Order	Shipment Status <tbody> </tbody>
2	1	EXW <tbody> </tbody>		8 <tbody> </tbody>	waiting info <tbody> </tbody>		15	COB <tbody> </tbody>
3	2	COB <tbody> </tbody>		10 <tbody> </tbody>	COB <tbody> </tbody>		9 <tbody> </tbody>	waiting info <tbody> </tbody>
4	5	DDP <tbody> </tbody>		5 <tbody> </tbody>	confirmed <tbody> </tbody>		6 <tbody> </tbody>	confirmed <tbody> </tbody>
5	3	booked <tbody> </tbody>		4 <tbody> </tbody>	booked <tbody> </tbody>		12 <tbody> </tbody>	booked <tbody> </tbody>
	Book2
	C	D
1	Purchase Order <tbody> </tbody>	Shipment Status <tbody> </tbody>
2	10 <tbody> </tbody>	Return 'COB' <tbody> </tbody>

<tbody>
</tbody>

I've been trying with multiple vlookups with an indirect function built in. I'm sure I only need an extended Index Match, as the PO number is the only criterion, but appear to be having issues with correlating the status value from the 3 different sheets.

The MrExcel guides work fine if I have the formula in the same workbook but as soon as I try to move it the links appear to break

For Book2, Sheet4, Cell=D2

=VLOOKUP(C2,INDIRECT("'"&INDEX(A2:A4,MATCH(TRUE,COUNTIF(INDIRECT("'"&A2:A4&"'!G2:G5"),C2)>0,0))&"'!G2:H5"),2,0)

-in the above I had my 3 sheets saved as a 'Sheet' list in cells A2:A4 on Sheet4 in Book1, and the formula worked fine

=VLOOKUP(C2,INDIRECT("'"&INDEX(Book1!Sheet,MATCH(TRUE,COUNTIF(INDIRECT("'"&Book1!Sheet&"'!G2:G5"),C2)>0,0))&"'!G2:H5"),2,FALSE)

-the above is where I have tried to link to the original book, but only appears to return #REF errors. Have I just accidentally missed something?

I've tried to edit a Vlookup/Indirect but with no success
=VLOOKUP(C2,INDIRECT("'"&LOOKUP(REPT("z",255), IF(COUNTIF(INDIRECT("'"&Sheet&"'!A2:A4"),C2),Sheet))&"'!A2:B5"),2,FALSE)

I've also tried a multiple Index/match combo which worked for some of the values but not others - I think only reading parts of the first sheet (when I can manually find many more), and returns mainly #N/A errors

=INDEX('[Book1.xlsm]Sheet1'!$H$2:$H$5&'[Book1.xlsm]Sheet2'!$H$2:$H$5&'[Book1.xlsm]Sheet3'!$H$2:$H$5,MATCH($C2,'[Book1.xlsm]Sheet1'!$A$2:$A$5&'[Book1.xlsm]Sheet2'!$A$2:$A$5&''[Book1.xlsm]Sheet3'!$A$2:$A$5,0))

Not very keen to code VBA but if there's a simpler solution would be open to hearing it as the manual checking and updating of this file takes forever so any streamlining solutions would be appreciated.

Many thanks in advance.