VLOOKUP  Trying to assign a task based on a product Line
I am very unfamiliar with working with funtions and VLOOKUPs. I have created a form that populates a sheet. I would like it to automatically assign the project that comes in based on the product line chosen. I created a separate sheet with the Names and the associated product lines. I have been trying to create the VLOOKUP but just keep getting #UNPARSEABLE back no matter what adjustments I make.
This is what I am typing: =VLOOKUP(IORT,{Table for reference  Marcom Range 2}:Robert, 2). I have added added spaces, I have put a 1 and a 2 after Robert but I can't figure it out.
However I also need it to pull based on the Product line answer that goes into the original sheet. WHich I think I am not doing properly.
This is a snippet of the sheet that the form populates into:
This is the table I am trying to pull from:
Does any of this make sense? Could anyone assist and/or direct me to where to learn more about the functions. The learning portal isn't clear enough for me.
Thanks
Best Answers

Hi Sheryl,
I'm happy to help explain how a VLOOKUP works!
1 . Unique Criteria to Match
So the first thing to put in a VLOOKUP is the Search Value...what are you looking for? In your case, you will want to match the values in the Product Line column in the sheet where the form comes in with the same value in the sheet with the table, under the same Product Line column. So to state this, we can use a column & row reference like so: [Product Line]@row
=VLOOKUP([Product Line]@row
2 . Table to Reference
Next, we need the table we're looking in. It sounds like you've labeled this {Table for reference  Marcom Range 2}. This should span across both columns. The VLOOKUP will read in the first column for what we specified (Product Line).
=VLOOKUP([Product Line]@row, {Table for reference  Marcom Range 2}
3 . Column Number with Value to Return
Finally, we say what column has the value we want returned. In this case it's going to be the second column in table you selected, since the "Assign To" column is right next to the Product line column:
=VLOOKUP([Product Line]@row, {Table for reference  Marcom Range 2}, 2
4 . Final Formula: Exact Match
(OPTIONAL)
There is an additional instruction you can add, which is to say whether or not it should look for an exact match or an approximate match. For an exact match you can add the word false to the end:
=VLOOKUP([Product Line]@row, {Table for reference  Marcom Range 2}, 2, false)
Try the final formula and let me know if it works for you! Here are some Help Center articles I used:
Cheers,
Genevieve

Hi @Sheryl Walder,
The two rows before have to have the same structure. See below.
If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.
Conditions That Trigger Formula Autofill
You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:
 Directly between two others that contain the same formula in adjacent cells.
 At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
 At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
 Above or below a single row that is between blank rows and has formulas
More info:
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers

Hi Sheryl,
I'm happy to help explain how a VLOOKUP works!
1 . Unique Criteria to Match
So the first thing to put in a VLOOKUP is the Search Value...what are you looking for? In your case, you will want to match the values in the Product Line column in the sheet where the form comes in with the same value in the sheet with the table, under the same Product Line column. So to state this, we can use a column & row reference like so: [Product Line]@row
=VLOOKUP([Product Line]@row
2 . Table to Reference
Next, we need the table we're looking in. It sounds like you've labeled this {Table for reference  Marcom Range 2}. This should span across both columns. The VLOOKUP will read in the first column for what we specified (Product Line).
=VLOOKUP([Product Line]@row, {Table for reference  Marcom Range 2}
3 . Column Number with Value to Return
Finally, we say what column has the value we want returned. In this case it's going to be the second column in table you selected, since the "Assign To" column is right next to the Product line column:
=VLOOKUP([Product Line]@row, {Table for reference  Marcom Range 2}, 2
4 . Final Formula: Exact Match
(OPTIONAL)
There is an additional instruction you can add, which is to say whether or not it should look for an exact match or an approximate match. For an exact match you can add the word false to the end:
=VLOOKUP([Product Line]@row, {Table for reference  Marcom Range 2}, 2, false)
Try the final formula and let me know if it works for you! Here are some Help Center articles I used:
Cheers,
Genevieve

Genevieve  Thank you. I got the VLookup to work. I do have another question  the look up is done once a form is submitted. How can I get the formula to copy into all of the rows as they are submitted? When I try to drag/copy the formula on the sheet, the new data enters at the bottom past where I stopped copying. I thought I read the formula automatically copies when a row is added but that is not happening.
Thanks

Hi @Sheryl Walder,
The two rows before have to have the same structure. See below.
If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.
Conditions That Trigger Formula Autofill
You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:
 Directly between two others that contain the same formula in adjacent cells.
 At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
 At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
 Above or below a single row that is between blank rows and has formulas
More info:
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
Check out the Formula Handbook template!