Thread: Reference a Cell for Tab name in VLOOKUP Table Array

Hello all -

I am trying to do the following:

Sheet 1 contains:

Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)

Sheet 2 contains:

Item Data
ABC 2

I'm trying to get the vlookup to return the value "2"

Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.

The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?

I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.

I apologize if this wasn't clear but it seems to be a slightly convoluted question.

Thanks in advance for the help.

Does your sheet2 tab really have a space in it? Sheet 2? That's not standard, and spaces in sheettabs make these formulas a little more confusing to look at....consider NOT having spaces.

INDIRECT is the function you want.

Without spaces in the tab name:
=VLOOKUP(A2,INDIRECT($B$1&"!$A:$B"),2,FALSE)

With spaces in the tab name:
=VLOOKUP(A2,INDIRECT("'"&$B$1&"'!$A:$B"),2,FALSE)

INDIRECT() used this way constructs a normal reference but does it one piece at a time. All the components have to be represented within the INDIRECT() brackets to resolve a normal cell or range reference.

==============

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].



(Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)