VLOOKUP to an external workbook

[trillicomm]

VLOOOKUP work fine if everything is in the same workbook. But I can't make a VLOOKUP to look into another workbook. I need your help to point out what the steps are to make VLOOKUP work among multiple workbooks. Please allow me to explain if this does not clarify the problem.

Waiting to hear from anyone!

T.

 

[trillicomm]

If you don't want to take the time to format ur column, using pennysaver's example with a slight edit, should pull in the result, without having to format.

=VLOOKUP(text(A1,0),'[The Other Workbook.xls]Sheet Name'!$C$12,1,FALSE)

Thank you Cam!!! You're awesome!

 

[vijayendra16]

I have a problem where i am unable to select the data from a separate workbook. I have both the workbooks open-the one i am tyoing vlookup in and the one that i have to reference the vlookup from.I type =vlookup(b31, after this when i click on the other workbook and try to select, nothing happens.the columns get selected as a normal selection and not the blinking one which happens in normal vlookup selection.when i go back to the excel where i was typing the formula, the formula is still there waiting for the array.If i copy the entire data from the 2nd workbook to a sheet in the 1st workbook (where i am typing the formula), then the vlookup works fine.I don't know why it started but what i could think is that after one of my colleagues created a VB file on my system is when it started.I have deleted all the VB and macros file but it still does not work.
Please help!

 

[sachin_10]

Sub VLOOKUP1()
Application.ScreenUpdating = False
Workbooks("excel1.xlsx").Activate
Sheets(1).Activate
'insert two cloumns
Range("b:b").Insert shift:=xlShiftToRight
Range("b:b").Insert shift:=xlShiftToRight

Range("A1").Select 'set the begin range as your choice

While ActiveCell <> ""
Application.StatusBar = ActiveCell.Row & " " & AvtiveCell
SName = ActiveCell
SMatch = owner1(SName)
If SMatch <> "" Then
ActiveCell.Offset(0, 1) = SMatch
End If
ActiveCell.Offset(1, 0).Select
Wend
Application.StatusBar = False

Range("A1").Select 'set the begin range as your choice

While ActiveCell <> ""
Application.StatusBar = ActiveCell.Row & " " & AvtiveCell
SName = ActiveCell
SMatch = issue1(SName)
If SMatch <> "" Then
ActiveCell.Offset(0, 2) = SMatch
End If
ActiveCell.Offset(1, 0).Select
Wend
Application.StatusBar = False

' For sheet2 vlookups

Workbooks("excel1.xlsx").Activate
Sheets(2).Activate
'insert two cloumns
Range("b:b").Insert shift:=xlShiftToRight
Range("b:b").Insert shift:=xlShiftToRight

Range("A1").Select 'set the begin range as your choice

While ActiveCell <> ""
Application.StatusBar = ActiveCell.Row & " " & AvtiveCell
SName = ActiveCell
SMatch = owner2(SName)
If SMatch <> "" Then
ActiveCell.Offset(0, 1) = SMatch
End If
ActiveCell.Offset(1, 0).Select
Wend
Application.StatusBar = False

Range("A1").Select 'set the begin range as your choice

While ActiveCell <> ""
Application.StatusBar = ActiveCell.Row & " " & AvtiveCell
SName = ActiveCell
SMatch = issue2(SName)
If SMatch <> "" Then
ActiveCell.Offset(0, 2) = SMatch
End If
ActiveCell.Offset(1, 0).Select
Wend
Application.StatusBar = False


End Sub


Function owner1(srch) As String
Set c = Workbooks("excel2.xlsx").Sheets(1).Columns("A").Find(srch, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
owner1 = c.Offset(0, 1) 'Value of column Q in the same row
Else
owner1 = "NA"
End If
End Function
Function issue1(srch) As String
Set c = Workbooks("excel2.xlsx").Sheets(1).Columns("A").Find(srch, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
issue1 = c.Offset(0, 2) 'Value of column Q in the same row
Else
issue1 = "NA"
End If
End Function

Function owner2(srch) As String
Set c = Workbooks("excel2.xlsx").Sheets(1).Columns("A").Find(srch, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
owner2 = c.Offset(0, 1) 'Value of column Q in the same row
Else
owner2 = "NA"
End If
End Function
Function issue2(srch) As String
Set c = Workbooks("excel2.xlsx").Sheets(1).Columns("A").Find(srch, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
issue2 = c.Offset(0, 2) 'Value of column Q in the same row
Else
issue2 = "NA"
End If
End Function

 

[sachin_10]

macro

Sub VLOOKUP1()
Application.ScreenUpdating = False
Workbooks("excel1.xlsx").Activate
Sheets(1).Activate
'insert two cloumns
Range("b:b").Insert shift:=xlShiftToRight
Range("b:b").Insert shift:=xlShiftToRight

Range("A1").Select 'set the begin range as your choice

While ActiveCell <> ""
Application.StatusBar = ActiveCell.Row & " " & AvtiveCell
SName = ActiveCell
SMatch = owner1(SName)
If SMatch <> "" Then
ActiveCell.Offset(0, 1) = SMatch
End If
ActiveCell.Offset(1, 0).Select
Wend
Application.StatusBar = False

Range("A1").Select 'set the begin range as your choice

While ActiveCell <> ""
Application.StatusBar = ActiveCell.Row & " " & AvtiveCell
SName = ActiveCell
SMatch = issue1(SName)
If SMatch <> "" Then
ActiveCell.Offset(0, 2) = SMatch
End If
ActiveCell.Offset(1, 0).Select
Wend
Application.StatusBar = False


End Sub


Function owner1(srch) As String
Set c = Workbooks("excel2.xlsx").Sheets(1).Columns("A").Find(srch, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
owner1 = c.Offset(0, 1) 'Value of column Q in the same row
Else
owner1 = "NA"
End If
End Function
Function issue1(srch) As String
Set c = Workbooks("excel2.xlsx").Sheets(1).Columns("A").Find(srch, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
issue1 = c.Offset(0, 2) 'Value of column Q in the same row
Else
issue1 = "NA"
End If
End Function