Vlookup function applied only to a filtered data in another sheet

Anastasija Popova

Hello, great Smartsheet community!

Could you please, help me to create a function that extracts data from another sheet, but only for data within a filter in question. This is what I have:

Sheet 1:

Columns: "Active" (Star), "Country+Disease " (text), "Status" (dropdown list, single value).

There are duplications of the values in the column "Country+Disease" with associated "Status" values. The rows of interest to me are filtered by a presence of a mark in "Active".

Sheet 2:

Columns: "Country+Disease " (text), "Tracking status" - the Vlookup column.

I am interested in receiving the values from the Sheet 1, "Status", only from the rows which have a star in the "Active".

In Sheet 2's "Tracking status" I use "Country +Disease" data as a search value to gain "Status" from Sheet 1. Naturally, I receive the value from the highest row in "Status" column in Sheet 1. I need only the filtered, Active ones.

I saw similar issues in this website, but they were so specific to the users, that I spent hours trying to fit it to my needs and failed. Please, help.

Leibel S

Your basic setup would be:

=INDEX(COLLECT({Sheet 1 - Status Range},{Sheet 1 - Country Range},[Country+Disease]@row,{Sheet 1 - Active Range},1),1)

Leibel S

@Anastasija Popova

You want to list all the statuses from Sheet1 or just the first one it finds that matches your criteria (country and active with star...).

You are probably looking for an INDEX and COLLECT formula.

Anastasija Popova

Thank you for the quick reply, @Leibel S!

I am looking for the just the first one it finds that matches my criteria (country and active with star...).

I have never used the mentioned formulas. I will have a look.

Leibel S

Your basic setup would be:

=INDEX(COLLECT({Sheet 1 - Status Range},{Sheet 1 - Country Range},[Country+Disease]@row,{Sheet 1 - Active Range},1),1)

Anastasija Popova

It worked! Magic! Thank you so very much @Leibel S!

I am very grateful for the people who take time to help others. I wish you all the best!

john munro

I think index and collect might help me to build a matrix sheet to look at another sheet or sheets and collect the information in Column B if Column A = todays date. What I am trying to do is simply list the jobs(tickets) that are due on the date listed in that rows Date column. The calendar view kind of does that but I want to build it into a dashboard so that stakeholders can see on the dashboard daily what jobs(tickets) are due today.

If I could get that working we would be off to the races. Now we do all kinds of manual work arounds to see what jobs are due each day.