VBA vlookup multiple sheets with multiple ifs

[Sipirili2504]

Hi. Appreciate it if you could advise me on how to convert this to VBA.
If column A2 contains apple, lookup in sheet appleweekly etc

=Vlookup(C2, IF(A2="APPLE", APPLEWEEKLY!A:Z, IF(A2="ORANGE", ORANGEWEEKLY! A:Z, IF(A2="LEMON", LEMONWEEKLY!A:Z))),5,0)

 

[Phuoc]

Try this:

= Application.VLookup(Range("C2"), Sheets(Range("A2") & "WEEKLY").Range("A:Z"), 5, 0)

 

[Sipirili2504]

Hi Phuoc. Thanks for the prompt response.

I have the below macro. It does not seems to work.
I am getting an error at the formula itself.
To make things more clear, I need this formula to be in the Master sheet at column AD2 to last row.
If column A2 is apple in Master sheet, I need to do a vlookup and see if the data in column C2 exist in the Appleweekly sheet and return data from column 5 to Master sheet column AD2.
If column A2 is orange, I need to do a vlookup and see if the data in column C2 exist in the Orangeweekly sheet and return data from column 5 to Master sheet column AD2.
Appreciate any help.

Dim RC as integer
RC = Range("A" & Rows.Count).End(xlUp).row

Range ("AD2").formula = "= Application.VLookup(Range("C2"), Sheets(Range("A2") & "WEEKLY").Range("A:Z"), 5, 0)
Range("AD2").Copy
Range("AD2:AD" & RC.pastespecialxlpasteall
Application.Cutcopymode= false