VLOOKUP - Trying to assign a task based on a product Line

Sheryl Walder · 04/24/20

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

Genevieve P. · 04/27/20

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

Andrée Starå · 04/29/20

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:

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

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!

Genevieve P. · 04/27/20