# Need help with a VLOOKUP and MATCH formula

Options
✭✭✭✭✭

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

Tags:

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

I've also tried

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

• ✭✭✭✭✭✭
Options

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)

Did that work?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Excellent!

You're more than welcome!

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.

• Options

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)

• ✭✭✭✭✭✭
Options

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

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!