VLOOKUP with another sheet with matching data

Jennifer Workman

Hello,

I am trying to do a VLOOKUP from another sheet based on a value in my current/new sheet. I want to MATCH the Task Name column first, THEN go to column 41(old sheet) and enter the date from that column onto my new sheet.

This is what I have, but I am a bit lost in the weeds. I could be very wrong about my code. Any help is appreciated!

=IF(VLOOKUP(([Task Name]8), {Old Range 1}, 1, [false])=[Task Name]8,"(VLOOKUP(ISDATE{Old Range 2},41,false))"," ")

Andrée Starå

Hi Jennifer,

If you don’t need the 40 other columns I’d recommend using an INDEX/MATCH combination instead or moving the columns closer to each other.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Would that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Because there is a limit to how many cells can be referenced and you are using a table of 41 columns in your cross sheet reference, I actually suggest an INDEX/MATCH. It is much more flexible, and in this case requires a lot fewer cells to be referenced.

Try something like this...

=INDEX({Other Sheet Date Column}, MATCH([Task Name]@row, {Other Sheet Task Name Column}, 0))

Andrée Starå

Hi Jennifer,

If you don’t need the 40 other columns I’d recommend using an INDEX/MATCH combination instead or moving the columns closer to each other.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Would that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Because there is a limit to how many cells can be referenced and you are using a table of 41 columns in your cross sheet reference, I actually suggest an INDEX/MATCH. It is much more flexible, and in this case requires a lot fewer cells to be referenced.

Try something like this...

=INDEX({Other Sheet Date Column}, MATCH([Task Name]@row, {Other Sheet Task Name Column}, 0))

Jennifer Workman

Paul and Andree,

Thank you very much! Paul - I have used that formula and it is successful. Definitely closer to my goal (and I can use this one somewhere else for sure)... but here is my next question for this use. I want to match the [Task Name] of the previous sheet (2) to the new one (1) , and then have it check for a date in a different column (after matching the [task name]) on sheet (2). If a date is found, then have it copy that date to my formula column on (1) - (I do not want to keep this linked, just to grab it one time) and then if no date is found, keep it blank. Can you help me? Note: I will be using a different column/row on sheet (1) for [Task Name] that I will match to sheet (2) first, then have the date go in a different cell on sheet (1).

Paul Newcome

@Jennifer Workman Using my method of the INDEX/MATCH would require you to manually remove the link once the date is pulled. Unfortunately there is no way in Smartsheet to do an automatic one-time pull the way you are wanting. It would be a great feature though, so feel free to Submit a Product Enhancement Request for that.

In the meantime, there may be a way to set it up using a third party tool such as Zapier, but that would definitely be something I would have to defer to @Andrée Starå on.

Jennifer Workman

Oh gotcha. So to clarify, as of right now, there is no way to compare 2 columns on 2 different sheets that match, and then take the value of a cell from one of their rows and put it on the formula cell on the new sheet.

Paul Newcome

Not as a one-time pull.

Jennifer Workman

Would it be possible to have it pull it and then be able to copy the data and replace the formula? If so, I would be interested in the formula.

Paul Newcome

It would have to be manually replaced for each row. There may be a way to pull it with Zapier, but that's a 3rd party tool I am not very familiar with. I only use it for one thing, and that's something I shouldn't have to touch again.

Andrée Starå

@Jennifer Workman Happy to help!

Let me know if you need additional help with Zapier or something else!

Have a fantastic weekend!