I need to effectively use multiple If/Vlookup functions. i.e. if it's
Oranges, Lookup price history in "oranges worksheet". But I have too many
categories to search in so this is not possible. So I had another solution,
but can't make it work...

In this case I am using a VLOOKUP formula and want to use one cell to search
on (a1) and another cell to tell it which spreadsheet to search in (Sheet2)

i.e. =Vlookup(a1,'[WorkbookData.xlsx](CellA2 i.e.Worksheet Name),2,False)

So is there a way to create a formula that uses text from another cell?