Vlookup to another sheet and the use of @cell / @row

Joh

Hi,

I'm using a Vlookup formula (=VLOOKUP(true, {ABC Range 3}, 3, false)) to get back information from another sheet (ABC) only when a row is flagged.

When dragging the current formula I only manage to get the value from the first row that is flagged. I guess that @row or @cell could help me solving my problem but I didn't manage to do it...

Could someone help me on this ?

Thanks

Andrée Starå

@Joh

You can still use the VLOOKUP, but you'd either filter the information or collect everything and then use a filter in the destination sheet to only show those that are flagged.

Make sense?

Mike Wilday

Could you provide some screenshots of your sheet, and the connected sheet without sharing sensitive data? That will help us determine what is going on?

Joh

Hi Mike,

here is the Smartsheet use as a source. So when flag is ON, I would like to get info from that row

and here is the destination Smartsheet. I manage to get the owner for the first row with a flag but then I can't get owner for the next flagged row.

I hope it is more clear.

Andrée Starå

Hi Joh,

It’s because you’ll need to have a unique Value for the VLOOKUP to get the correct information.

Is there anything unique that you could use in the source sheet?

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 help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Joh

Hi Andrée,

unfortunately not.

Do you have other method that could solve this ?

Basically, i'm trying to do what a report is doing, but i need to do it in a normal sheet because I need to add extra columns with computation.

thanks for your help.

Joh

Andrée Starå

Happy to help!

How much information is it?

We could either add auto-number columns on both sheets and use that as the unique identifier or use cell-linking.

I'd recommend the first option if possible.

What do you think?

Linda F

Andree

I need a similar formula and I do have a unique identifier - a project #. I want to reference data from another sheet row to pull into my newly created project sheet. The data from Sheet A was collected from a form. I created a project summary and need the row to populate only certain columns for a Project #. I used a define formula but only received 1 cell data. Can you help?

Joh

Hi Andrée,

I'm not sure to understand how to use this auto numbered column with my vlookup. What does the formula will look like if i add an auto number column just before my flag column (in data source) ?

thanks

Andrée Starå

@Linda F

Sure!

What's the formula you're using now?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Andrée Starå

@Joh @Linda F

The VLOOKUP would look something like this.

=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}; 2 <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)

Syntax

VLOOKUP(search_valuelookup_tablecolumn_num[match_type])

• search_value—The value to search for, which must be in the first column of lookup_table.
• lookup_table—The cell range in which to search, containing both the search_value (in the leftmost column) and the return value.
• column_num—A number representing the column position (in lookup_table) of the value to return, with the leftmost column of lookup_table at position 1.
• match_type—[optional] The default is true. Specifies whether to find an exact match (false) or an approximate match (true).

More info:

https://help.smartsheet.com/function/vlookup?frame=0&nav=1&dlm=112

Hope that helps!

Did you get it working?

Joh

Thanks Andrée.

I guess this vlookup won't help me because i have more than thousands line and from that, i only need to take those that are flagged, and it will evolve with time (don't have control on this) .. so direct cell linking or auto numbered is not an option.

I really need to do the same as a Report is doing, by pulling data from on sheet when a trigger is on (flag in my case). but without using the report option..

if you think to another solution, i'll be happy to try it.

thanks

Andrée Starå

@Joh

You can still use the VLOOKUP, but you'd either filter the information or collect everything and then use a filter in the destination sheet to only show those that are flagged.

Make sense?

Joh

thanks @Andrée Starå . I will do that

Linda F

Hi Andree

Sharing what I have ( =IFERROR(VLOOKUP(Project Name, {Facilities Request - Project Name}, 4, false), "-"), Snapshot below of what I am attempting. The data comes from 2 separate sheets. 1 captures estimates and work done and the other captures the project schedule. Eventually, this will be used to create a report and dashboard.

Andrée Starå

@Linda F

Try this.

=IFERROR(VLOOKUP([Project Name]@row, {Facilities Request - Project Name}, 4, false), "-")

Or maybe change the Project Name to Status@row.

Did that work?

Linda F

It looks like it has worked for most of the fields except Project Name. Snapshot below