Creating a vlookup formula table array address using a drop down box

QuestionEdit

I am trying to create a vlookup formula that would allow me to change the table_array details depending on what is selected by from a dropdown box,(sheet1,sheet2,sheet3,sheet4) ie vlookup(A1,dropdownbox!a1:b99,2,false) the cells within the table_array address would stay the same, its just the sheet selected via the dropdown that i would want to change.

hope this makes sense and

AnswerEdit

KEVIN

If I am understanding correctly, the formula might look something like this.

IF(A1="SHEET2",+VLOOKUP(A2,Sheet2!A1:B20,2,FALSE))+IF(A1="SHEET3",+VLOOKUP(A2,Sheet3!A1:B20,2,FALSE))+IF(A1="SHEET4",VLOOKUP(A2,Sheet4!A1:B20,2,FALSE))

As you can see the vlookup goes to a differenct sheet depending on what is selected in the drop down box in cell A1 if sheet2 is selected then it goes to the array in sheet2, if sheet3 is selected then it goes to the array in sheet3, etc. etc. Of course your references will be different and the number of sheets that you are using for your lookup arrays will be different but this should give you the idea.

Richard