VLOOKUP formula to copy values from one sheet to another

Hey Guys,

I Have a sheet name "Holiday", and I want to copy the cols "Country", "Start Date" and "End Date" from this sheet to another (empty) sheet called "Holidays Israel", BUT only the rows that thier country is Israel. I tried VLOOKUP (this is the formula I used: =VLOOKUP("Israel", {Holidays Range 3}, 5) but when I drag the formula to the rows below it copies the same one date.

How can I copy all the different dates which belong to Israel?

Attaching screenshots

This is the "Holidays" sheet

this is the sheet I want to copy the dates to:

Thank you!


  • David Tutwiler
    David Tutwiler Overachievers Alumni

    I would use the Copy Row automation. If you set it for something like "When rows are added or changed" and when Country = Israel, then it should move any new holidays over to your new sheet.

    In order to get it to take your existing rows, you could go and rename your country from Israel to "I" and click save, then go back and change them back to "Israel" and click save 1 last time. That should move all of your rows to your new sheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!