Need help with a VLOOKUP and MATCH formula

jmhoward

Hi there,

I confess I get so lost when it comes to formulas. I've read through various community discussions and help pages, but for some reason I just cannot get the logic of creating a formula.

In sheet 1 I have a column for "Freelancer." In sheet 2, I have a one column for the "Freelancer" and one column for "Payee," which is how we should make the check out to the Freelancer. For example, Bob Smith is our Freelancer and the Payee is "Bob Smith Enterprises."

In sheet 1, I want the Payee column to auto-populate based on the information in sheet 2. So, in sheet 1, when someone enters "Bob Smith" as the Freelancer, the Payee column should auto-populate with "Bob Smith Enterprises."

I'm fairly certain I need to use the VLOOKUP and MATCH functions, but I keep getting unparsable errors. This is what I tried:

=INDEX(Freelancer):(Freelancer), MATCH({Payee Lookup}@row, {Freelancer Lookup}:{Freelancer Lookup}, 0))

In which:

Freelancer = the Freelancer in sheet 1 (e.g., Bob Smith)

Payee Lookup = the Payee in sheet 2 that should auto-populate in sheet 1 (e.g., Bob Smith Enterprises)

Freelancer Lookup = the Freelancer name in sheet 2 that should match to the Freelancer name in sheet 1 (e.g., Bob Smith)

I know I'm way off, but I just can't figure out how to fix this.

Thanks!

Jackie

jmhoward

Yes, that worked! Thank you so much for your guidance!

jmhoward

I've also tried

=INDEX({Payee sheet 2}:{Payee sheet 2}, MATCH("Freelancer", {Freelancer Lookup}:{Freelancer Lookup}, 0))

Andrée Starå

Hi @jmhoward

Here's an example of a VLOOKUP formula.

=VLOOKUP([Destination Sheet Column]@row; {Source Sheet Range}; 5; 0)

=VLOOKUP([Column with the value to match against]@row; {Source sheet range where the first column in the range has to contain the value we want to match}; 5 <This number we use to select the column from where we want to get the value, and from the starting column; 0<Indicates that we want to find an exact match)

More info

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

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

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

Did that work?

I hope that helps!

Be safe and have a fantastic day!

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!

jmhoward

Yes, that worked! Thank you so much for your guidance!

Andrée Starå

@jmhoward

Excellent!

You're more than welcome!

✅Remember! 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!

Tanya Brooks

Hi Andree -

I am trying to do somethign similar and need help.

I need to assign a sort value so that title can be sorted by rank. I set up a source sheet with rank & sort number.

I want to insert a formula in the "SORT" column in my destination sheet that says look at value of "Title" in destination sheet and return "Sort" number from source sheet. EX: if Title ="Instructor" assign sort value 1; if Title = "Assistant Professor" assign sort value 2, etc.

I think I want Vlookup but cannot get syntax correct.

Rank Sort sheet (source sheet)

Destination sheet (want formula in SORT olumn to auto fill)

Andrée Starå

Hi @Tanya Brooks

Apologies, I missed your post.

Did you get it working or do you still need help?

Be safe and have a fantastic weekend!

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!