SOLVED

VLOOKUP on a table selected by a dropdown...

Copper Contributor

VLOOKUP on a table selected by a dropdown...

Hello,

I’ve got a question, is it possible in Excel 365 to obtain a number on a cell of a VLOOKUP on a worksheet which is selected by a dropdown…?

Here is what I am trying to do:

In a worksheet called "C" I’ve created a dropdown with two options "A" or "B".

"A" is a worksheet which contains a table with data and "B" is another worksheet which also has a table but with different data on it.

Depending the selection in the dropdown (either "A" or "B") a cell in worksheet "C" has to return a number using a VLOOKUP on the correct worksheet selected by the dropdown ("A" or "B")…

Thanks, Martin

4 Replies

Re: VLOOKUP on a table selected by a dropdown...

@Martin_Colombetti

Let's say that:

The lookup value is in cell A2.

The drop down is in cell B2.

The lookup range is the same on both sheet A and sheet B: A2:D100.

You want to return a value from column D.

=VLOOKUP(A2, INDIRECT("'"&B2&"'!A2:D100"), 4, FALSE)

Re: VLOOKUP on a table selected by a dropdown...

Hi Hans,
thanks for your reply, I've tried your formula but it dosn't work... I must be doing something wrong... it returns "You've entered too many arguments for this function".
Here's what I typed:
=VLOOKUP(B35,INDIRECT("'"&B5&"'sheet_B!A21:E36",5,0)
best response confirmed by VI_Migration (Silver Contributor)
Solution

Re: VLOOKUP on a table selected by a dropdown...

You omitted a closing parenthesis. And you shouldn't include the literal sheet name - it is already referenced in cell B5.

=VLOOKUP(B35,INDIRECT("'"&B5&"'!A21:E36"),5,FALSE)

Re: VLOOKUP on a table selected by a dropdown...

@Hans Vogelaar awesome!!! thanks a lot, now is all working

1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

Re: VLOOKUP on a table selected by a dropdown...

You omitted a closing parenthesis. And you shouldn't include the literal sheet name - it is already referenced in cell B5.

=VLOOKUP(B35,INDIRECT("'"&B5&"'!A21:E36"),5,FALSE)