Multiple workbook Match and Index

[Linda]

I have two workbooks. One is produced from a routine run - Workbook1.xls.
The other is a static file with a lookup table in - Lookup1.xls. I want to
take a cell value from Workbook1.xls, find that value in column 1 of
Lookup1.xls, and return a corresponding value from column 3 of Lookup1.xls.
I have been able to do it by putting a formula in a cell in Workbook1 but I
need to do it as part of a macro. The formula I have now is

=INDEX(Lookup1.xls!$A:$C,(MATCH(A2,Lookup1.xls!$A:$A,0)),3)

How do I convert this to a macro? I am running into a problem referencing
the second workbook.

Thanks.

 

[Tom Ogilvy]

Dim rng as Range
Dim rng1 as Range
Dim rng2 as Range
Dim res as Variant
set rng1 = ActiveSheet.Range("A1")
with workbooks("Lookup1.xls").worksheets("Sheet1")
set rng = .range(.cells(1,1),.cells(rows.count,1).End(xlup))
End with
res = Application.Match(rng1,rng,0)
if not iserror(res) then
set rng2 = rng1(res).offset(0,2)
msgbox rng2.value
else
msgbox rng.Value & " Not found"
end if

since you don't show the sheet name in your formula and don't properly
address the workbook name, I assume the formula is representative of what a
real formula would look like. I assumed for illustration purposes the sheet
name in Lookup1.xls is Sheet1. Change to match. Use something like the
above untested pseudocode.
If you are looking up a date, then change

res = Application.Match(rng1,rng,0)

to

res = Application.Match(clng(rng1),rng,0)