Assistance with a formula -- similar to VLookup

Lainie Franklin

I’m not sure if this is possible in Smartsheet but I know the Community will know!

For our Annual Meeting, I created a blank sheet that will help us manage our schedule and assigned meeting rooms. Some of the columns are:

• Meeting Room Name (the name of the hotel meeting room)
• Capacity-Conference (the capacity of the hotel meeting room if set up in conference style)
• Capacity-U-shaped (the capacity of the hotel meeting room if set up in u-shaped style)
• Capacity-banquet (the capacity of the hotel meeting room if set up in banquet style)
• Floor (the floor of the meeting room)
• Number of Attendees (the number of people who will attend the session)

My goal is to assign a room and have all the other fields prepopulate.

For example, if the [Meeting Room Name] = Constitution, then Capacity-Conference would display 16, Capacity-U-shaped would display 20, Capacity-Banquet would display 20, Floor would display Lower.

I have all that information in a different sheet but I don’t believe Smartsheet can currently look up information from one sheet and populate another. From what I see, my only option is a fairly long nested if statement.

Any ideas?

Thank you for your help! 

 

Lainie

Robert S.

Hello Lainie,

 

Thanks for the question. Smartsheet formulas don't currently have the ability to reference other sheets. This functionality is being worked on however, and we hope to have this ready to release in Q1. Please keep in mind that this could change, and we don’t have an exact date.

 

We do have a LOOKUP() function however, and this can be used to look up a value and return a corresponding value in the same row but from a different column. This works within the same sheet, so the lookup table information would need to be brought into the same sheet that the formulas are on. Here's a help center article with more information on the LOOKUP() function (https://help.smartsheet.com/function/lookup).

Lainie Franklin

Robert, 

Thank you for the reply. I'm not still understanding. So, I need to copy my chart that contains all of the information about the meeting rooms (capacity, etc.) into the original sheet? If so, would I do that at the bottom of the sheet? 

Then, I'm not sure how I tell Smartsheet to look at the range of cells that contain the information to look at. 

For clarity, let’s say that the information about the rooms is the Look Up Chart. So, I’ll paste the Look Up Chart into my original sheet on rows 20-27.

Now, I need a formula in Column [Capacity-Conference]2 that looks at column [Room]2 and "If Cabinet", look at rows [Room]20-27. "If equal to Cabinet", return the value of 64. Does that make sense?

I’m not sure how to write the formula for looking at a range of cells. I looked at the Index instructions, but I’m not sure that is it either.

I appreciate your help!

 

Robert S.

Hello Lainie,

 

That's correct, in order to do this the look up chart (data you want to look up information in, in this case the information about the rooms) needs to be on the same sheet as the formulas you'd like to create. As far as where to put this information, that's up to what's easiest for you. You can put this at the very top, very bottom, or even create all new columns off to the right side just for this information.

 

For now I'll go with the positioning you mentioned of rows 20-27, and use the column names from your first screenshot. If you're entering the name of the room in the "Room" column for row 2, and want the information for columns "Capacity-Conference", "Capacity- U-shaped", "Capacity-Banquet", and "Floor" automatically filled out to match the information in your look up chart, you can put a formula in each of those 4 columns. These formulas will use the LOOKUP() function, and will be very similar to one another with slight differences. Here's an example of what the formula for the "Capacity-Conference" column in row 2 could look like:

 

=LOOKUP(Room2, Room$20:Floor$27, 2)

 

This formula is using the LOOKUP() function (https://help.smartsheet.com/function/lookup), and utilizes absolute references (https://www.smartsheet.com/blog/relative-and-absolute-references).

Using the syntax in the help center article for the LOOKUP() function, I'll break this down. The first part of the function is the search value, which means whatever in put into the referenced cell "$Room2", is what to look for. The next part is the lookup table, or as we've been calling it the look up chart. This is the range of cells that contain both the search value and the information you want to return, in this case that's rows 20-27 and in the columns "Room" through "Floor". This is written as Room$20:Floor$27. The next part is the column number (in lookup table) of the value to be returned, with the leftmost column of lookup table at position 1. This is what's different between the formulas in the different columns. The column "Capacity-Conference" is 2, and the column "Capacity- U-shaped" would be 3.

 

Here's a screenshot to help illustrate this better.

Lainie Franklin

Thank you so much! This was a great help. Keep up the great work!

Lainie 

Shaine Greenwood

You can now reference data from other sheets in formulas!

Create a master lookup table sheet and use VLOOKUP to find data on another sheet.

More information on this new feature is available in our help center: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets