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

Melanie Sanders

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!

Genevieve P.

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

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

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!

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

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

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

Jessica Chandler

@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.

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