If Statement the vlookup across three sheets

[MAXIM]

Hi Forum,

I would like some help with what should be a quick fix.

in J11 I have a drop down box with options 1 2 3

What Im try to get is if J11=1 then vlookup from "Dealer Benchmarking visit 1" sheet
J11=2 then vlookup from "Dealer Benchmarking visit 2" sheet
J11=3 then vlookup from "Dealer Benchmarking visit 3" sheet

I have tried :

=IFERROR(VLOOKUP($J11,'Dealer Benchmarking Visit 1'!$A$3:$C$3,3,FALSE),IFERROR(VLOOKUP($J11,'Dealer Benchmarking Visit 2 '!$A$3:$C$3,3,FALSE),VLOOKUP($J11,'Dealer Benchmarking Visit 3'!$A$3:$C$3,3,FALSE)))

The result I get is #N/A

Any suggestions?

[Harribone]

You can just use INDIRECT in the vlookup but I can remember how to get it to work sorry, will update once I get it right if someone else doesn't beat me to it.

[ChemistB]

Okay, so if J11 =1 lookup on table in Visit 1", if 2 lookup on table in "Visit 2" and if 3, lookup on Table from "Visit 3" and the value you want to lookup is the same value you are using to determine which table to look it up on (1,2, or 3)???
Your tables on each of these sheets only include a single row (A3:C3) and you are always returning the value in C3 (according to your formulas)

You can use =INDIRECT("'Dealer Benchmarking Visit "&J11&"'!C3")
Would that work for you?

[MAXIM]

ChemistB

thanks for that but when I paste =INDIRECT("'Dealer Benchmarking Visit "&J11&"'!C3")

I get a #ref in my results cell

[MAXIM]

Thanks Harribone,

I wait in anticipation

[Harribone]

ChemistB's solution should work. You will get #REF if J11 is blank or contains any value which is not 1,2 or 3 as these are the only conditions the formula will work.
To get around this use =IFERROR(INDIRECT("'Dealer Benchmarking Visit "&J11&"'!C3"),"") which will leave the cell blank in conditions.