Use a variable in a (VLOOKUP) function

Question

I have a VLOOKUP: (well many)

=VLOOKUP(<lookup cell>, [<external workbook file>]<sheet name>!<range>, <col index>,false)

This works ok but instead of hard coding the sheet name I want to be able to switch the sheet name in the formula by replacing it with a variable, preferably a cell value in the same sheet as where the VLOOKUP resides.

Sheet name is then taken from a cell that can be changed to any of the sheet names in the workbook.

(Essentially I want to switch between several price lists, each on a sheet in an external workbook file).

Is this possible?

(I can use VBA if necessary but prefer not to)

Regards

Peter

Answer 1

Hi,

No need for VBA, try this where the worksheet name is in B1 of the same sheet the formula is in.

=VLOOKUP(A1,INDIRECT("'[Book2]" & B1 & "'!$A$1:$C$20"),3,FALSE)

Answer 2

Great, just one little detail missing. If the files is saved make sure you include the file extension and, with this example, the external file referenced must be open in Excel or you'll get an error.

E.g.:

=VLOOKUP($B2,INDIRECT("'[book-name.xlsx]" & $A2 & "'!A:B"),2,FALSE)

This broken down means:

=VLOOKUP(The value to look up,INDIRECT("'[book-name.xlsx]" & Cell where the sheet name is & "'!Range to lookup"),index column,FALSE)

Answer 3

Hi,

I am using the below below to check with dynamic workbook & worksheet !!!!

Private Sub CommandButton5_Click()

Dim fileToOpen As String, fullRangeString As String, mVal As Variant

Dim wb As Workbook, c As Integer, cell As Integer

Dim Nwb As Workbook, Nws As Worksheet, rng As Range

Dim i As Long, lrng As Range, Col As Variant, look As Variant, lookfor As Variant

cell = WorksheetFunction.CountIfs(Sheet5.Range("A:A"), "<>")

Set wb = ThisWorkbook

fileToOpen = Application.GetOpenFilename

If fileToOpen = "False" Then Exit Sub

If IsOpen(fileToOpen) = False Then

'Workbooks.Open (fileToOpen)

Set Nwb = Application.Workbooks.Open(fileToOpen)

Else

Windows(fileToOpen).Activate

End If

Set rng = Application.InputBox( _

Prompt:="Select a cell for the output.", Title:="Select a column", _

Default:=ActiveCell.Address, Type:=8)

Col = rng.Columns.Count

Application.ScreenUpdating = False

Application.EnableEvents = False

Columns("G:G").ClearContents

Columns("G:G").NumberFormat = "@"

For i = 2 To cell

Set lrng = Sheet5.Cells(i, 1)

If Application.WorksheetFunction.IsText(lrng.Value) = True Then

mVal = Val(lrng.Value)

Else

mVal = lrng.Value

End If

look = Application.VLookup(mVal, rng, Col, 0)

If IsError(look) Then

lrng.Offset(0, 6).Value = ""

Else

lrng.Offset(0, 6).Value = look

End If

Next i

Nwb.Close False

Application.EnableEvents = True

Application.ScreenUpdating = True

c = WorksheetFunction.CountIfs(Sheet5.Range("G:G"), ">*")

If c <> 0 Then

Cells(1, 7).Value = "Matched"

Columns(7).Font.Bold = True

MsgBox c & " MSISDNs Found !!"

Else

MsgBox "No MSISDN matched !!!"

End If

'Application.EnableEvents = True

'Application.ScreenUpdating = True

End Sub

Answer 4

Hi,

To make the formula work even if the workbook is closed, you may refer to my solution at this link - http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/

Hope this helps.

Answer 5

Mike,

Many thanks. Exactly what I needed!

As a sidenote to others reading this. The external file referenced must be open in Excel or you'll get an error.

Regards

Peter