VLOOKUP on Another Sheet

Amanda Gilroy

I'm a relatively new Smartsheet user and am struggling with some of the formulas. I want to do a VLOOKUP to pull data from one sheet to another but I am only getting the value from the first row.

On Sheet 1 - I want to return the value in the "Production" field, where the "Live Date Within 60 Days" value = "Yes" from Sheet 2.

I can only get the "Production" value (93) from the first row to return, for every row.

Sheet 1: =VLOOKUP("Yes", {Prod Defects}, 2, false)

Note that {Prod Defects} is my sheet reference range from Sheet 2.

Sheet 1:

Sheet 2:

Amanda Gilroy

I was making this much harder than necessary. Providing what I did here for others to reference.

Goal: I have a list of in-flight clients. My need was to pull a list of clients that have live dates within 60 days, report back their client name and the number of production defects they have.

In my SHEET, I had the following columns: Client Name, Production Defects, Live Date within 60 Days ("Yes" value if true).

I created a REPORT of these columns and then used a filter on the "Live Date within 60 Days" column to narrow my list to just those with "Yes".

I was then able to create a Chart (Column type) from that REPORT on my Dashboard and selected only the "Client Name" and "Production Defects" to display the data I want.

While I did find a seemingly simple solution, it was a challenge to get there. It doesn't seem very 'smart' that I have to create a report to narrow the data to allow me to generate the chart. It would be nice to have a feature built into the Chart functionality to allow me to create the Chart from the source sheet and then 'filter' the data to narrow what is displaying.

Hollie Green

It's because it finds that value as a yes first everytime. Do you have a column with a unique value that you could reference? If so I would try an Index/Collect formula that way if you ever need to rearrange columns on your reference sheet it won't mess up your formula as well.

Amanda Gilroy

@Hollie Green Thanks for the response. I tried the Index/Collect formula and I'm getting an #UNPARSEABLE error.

=INDEX(COLLECT({Live Dates within 60 Days}, {Go Live - Status Summary Range 3},"Yes")", 1")

{Live Dates within 60 Days} = name of same column in Sheet 2

{Go Live - Status Summary Range 3} = "Production" column from sheet 2

What am I doing wrong?

Amanda Gilroy

I was making this much harder than necessary. Providing what I did here for others to reference.

Goal: I have a list of in-flight clients. My need was to pull a list of clients that have live dates within 60 days, report back their client name and the number of production defects they have.

In my SHEET, I had the following columns: Client Name, Production Defects, Live Date within 60 Days ("Yes" value if true).

I created a REPORT of these columns and then used a filter on the "Live Date within 60 Days" column to narrow my list to just those with "Yes".

I was then able to create a Chart (Column type) from that REPORT on my Dashboard and selected only the "Client Name" and "Production Defects" to display the data I want.

While I did find a seemingly simple solution, it was a challenge to get there. It doesn't seem very 'smart' that I have to create a report to narrow the data to allow me to generate the chart. It would be nice to have a feature built into the Chart functionality to allow me to create the Chart from the source sheet and then 'filter' the data to narrow what is displaying.