Vlookup in entire workbook

[deepthi_r]

I have a workbook with 4 sheets - One each for copper, aluminium and Job Work and the 4th is the mastersheet which will have data collated from the other 3.

Data in the 3 sheets include Invoice no. (which is unique and the criteria for Vlookup) and corresponding dates and qty dispatched. In the mastersheet, the first column is invoice number (which is a series, but is spread across the above 3 sheets) i.e. inv no. 1 can be in copper while 2 will be in aluminium..

I need a Vlookup formula to search for the entire workbook for invoice no. (in copper sheet, if not found to move to Aluminium and then to Job work) and pull out the corresponding date and qty for the same.

Right now I am using a super complicated ISNA formula:

=IF(a4="","",IF(ISNA(VLOOKUP(I4,'\\server\Dispatch\[Stock Register 2014_15.xlsx]Copper'!$A:$C,3,0)),IF(ISNA(VLOOKUP(a4,'\\server\Dispatch\[Stock Register 2014_15.xlsx]Aluminium'!$A:$C,3,0)),VLOOKUP(a4,'\\server\Dispatch\[Stock Register 2014_15.xlsx]Job Work'!$A:$B,2,0),(VLOOKUP(a4,'\\server\Dispatch\[Stock Register 2014_15.xlsx]Aluminium'!$A:$C,3,0))),(VLOOKUP(a4,'\\server\Dispatch\[Stock Register 2014_15.xlsx]Copper'!$A:$C,3,0))))

But is there a simpler option? Am scared guys who use this might accidentally delete or tweak!

Dee.

[Vikas_Gautam]

Deepthi..
provide a sample or original non sensitive workbook...
this way we can help better..


waiting for *