indirect?

[mepetey]

I have 4 workbooks, named fish,meat,fruit and veg. Each of the workbooks is
populated with multiple worksheets, each worksheet is unique for one item
( as the list below).

apple
pear
lamb
chicken
carrot
potato

I have a master workbook that lists all of the items across all of the
workbooks, in column A. I need a formula in column B that will look up each
of the items in the relevant workbook and report what it finds in cell H2 in
the workbook. I can do the vlookup ok BUT I don't want to have to change
the formula for each individual item. I'm sure and indirect is involved but
I can't crack it. any ideas please?

 

[Ron de Bruin]

Indirect is not working with closed workbooks