Lookup Values in another sheet and bring back data based on city name

Hi,

I have a master sheet and have created a new spreadsheet for a project. I am wanting to see if it is possible to look at the city name from my my 1st sheet (ORIGINAL SHIP TO LOCATION) and bring data back from the master location sheet in a column titled STREET?

1st sheet name = MASTER Locations

2nd sheet name = IRE MASTER ORDERS

Does anyone know how to accomplish this?

Thx!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Melanie Sanders

    I can see that you marked the comments on this thread as not answering your question. Are you still having trouble setting up an INDEX(MATCH formula?

    I notice in your screen capture that your first range is not in {these} curly brackets. You may want to delete the words there and create a new Cross Sheet Reference from scratch to make sure you're pointing to the correct column. See: Formulas: Reference Data from Other Sheets

    If you create a cross-sheet reference by clicking the pop-up that says "Reference another sheet" it will automatically create the proper syntax of {Column Reference} for you. I notice in an earlier comment you had added extra {{ and had two instead of just { one, which would cause an error.

    Here's the formula structure:

    =INDEX({Column to return}, MATCH([Value to match]@row, {Column with value to match}, 0))


    Cheers,

    Genevieve

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi@gmb-nps-xofigo88916

    Hope you are fine, yes you can do that by using Index with Match functions. If you like I can do it for you but I need an admin sharing on a sample copy of your sheet after removing any sensitive data

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @gmb-nps-xofigo88916

    I hope you're well and safe!

    Here’s how to write an INDEX/MATCH formula.

    =INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; 

    {ColumnWithTheValueToMatchAgainsTheCell}; 0))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work? 

    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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Melanie Sanders
    Melanie Sanders ✭✭✭✭✭

    Hello @Andrée Starå,

    Thank you for the response. I appreciate your assistance. I am sure I don't understand what I need to do with your formula. This is what I did but it is unparseable :-(


    =INDEX({{MASTER LOCATIONS Range 1}}; MATCH(ORIGINAL SHIP TO LOCATION@row;

    {{MASTER LOCATIONS Range 1}}; 0))

    What information do you need from me to assist with my issue?

    Thank you!

    Melanie

  • Melanie Sanders
    Melanie Sanders ✭✭✭✭✭

    These are the two sheets I am working with. I am wanting to fill in the street address on the sheet 2 based off of the original ship to city on the same sheet. Sheet 1 is where the city reference is and the address to use. I have tried two different formulas but nothing is working. Any help is greatly appreciated.

  • Melanie Sanders
    Melanie Sanders ✭✭✭✭✭

    @Andrée Starå please see my last comment above.

  • Jessica Chandler
    Jessica Chandler ✭✭✭
    edited 07/20/21

    @gmb-nps-xofigo88916

    Maybe a VLOOKUP formula? Just keep in mind you may need a way to match the two items with a unique id of some kind to use for the search_value. The search_value must be in the leftmost column (position 1) of lookup_table.

    When using VLOOKUPS, I usually copy the column of search values and insert it in the leftmost column.

    Then once my data has matched up, I copy convert the formulas to data and delete the search value column (unless it's a task you will do repeatedly).

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Melanie Sanders

    I can see that you marked the comments on this thread as not answering your question. Are you still having trouble setting up an INDEX(MATCH formula?

    I notice in your screen capture that your first range is not in {these} curly brackets. You may want to delete the words there and create a new Cross Sheet Reference from scratch to make sure you're pointing to the correct column. See: Formulas: Reference Data from Other Sheets

    If you create a cross-sheet reference by clicking the pop-up that says "Reference another sheet" it will automatically create the proper syntax of {Column Reference} for you. I notice in an earlier comment you had added extra {{ and had two instead of just { one, which would cause an error.

    Here's the formula structure:

    =INDEX({Column to return}, MATCH([Value to match]@row, {Column with value to match}, 0))


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!