VlookUp between two workbook & different sheets

**yjmmay34** · 06-08-2010, 08:58 PM

Hi, All. I need the macro to look up the no. from the DataBase (sheet1 and sheet2 )then put the no. to the File1 which has 3 sheets. Please help with this. And check the attachment below. Thanl you so much!

**JBeaucaire** · 06-08-2010, 10:43 PM

Put this formula in F8 on any File1 sheet...then copy down:

=INDEX([DataBase.xls]Sheet1!$D:$D, MATCH(C8, [DataBase.xls]Sheet1!$A:$A, 0))

If the name is in the database, you will get a number, otherwise a Not Available (#N/A) response.

No macro needed for this.

**venkat1926** · 06-08-2010, 11:07 PM

I am returning your file file1.xls. In this file see the formula in sheet A cell F8.This is copied down in sheet A of column F till there is data in column C

copy the formula in this F8(repeat F8 of sheet A) in F8 of sheet B and sheet C
ine each of the sheests copy F8 down.

how to copy the formula

go to formula bar of F8 of sheet A of file1.xls
highlight the formula
click edit-copy
hit ESc key
select F8 of sheet B and type single apostrophe (') and hit control+V
hit entery key
go back ot F8 of sheet B and remove the single apostrophe(') in the beginning of the formula and hit enter key.
copy F8 sheet B down
do similar action with sheset C

I have configured the formula for possible 100 rows in datbase.xls file in both the sheets.

for completion sake the formula in F8 of sheet A in file files1.xls is given below

=IF(ISNA(VLOOKUP(C8,[DataBase.xls]Sheet1!$A$4:$D$100,4,0)),VLOOKUP(C8,[DataBase.xls]Sheet2!$A$4:$D$100,4,0),VLOOKUP(C8,[DataBase.xls]Sheet1!$A$4:$D$100,4,0))

**yjmmay34** · 06-08-2010, 11:44 PM

Hi,venkat. Thanks for the help. But i need a macro to auto run this program. Do you know how to write code to work out this part?

**yjmmay34** · 06-09-2010, 03:36 AM

HI, Jbeaucaire. Thank you. But this VlookUp is just a part of my program. Inside my macro there are other works to do, so i want this VlookUp macro then just put inside the whole program.