VLOOKUP/INDEX return client name from master

Michele Rosenberg

Hello - I am trying to create a dynamic dashboard where a client name can be changed based on need. I watched a video on YOUTUBE and it was very good but he was indexing on numbers. I need to match on latest name entered into form. I am not getting results, have tried VLOOKUP and INDEX. I now it is likely a simple mistake :)

Client Lookup VLOOKUP

=VLOOKUP(MAX({Client query form | Latest Client Name}@row, {Client Master | Client Name}, 1, false)

Client Lookup INDEX

=(INDEX({Client Master | Client Name}, MATCH(1, {Client query form | Latest Client Name}, 0)))

Client Name Query Form

Client Name Master

Additionally, I have set up the master in a dynamic view because I thought it made sense. I am not sure now that was necessary. I am looking to create dynamic dashboard.

Matt Lynn-PCG

@Michele Rosenberg This is a tricky one but it can be done.

Jumping to the end, you could do a dynamic view (or multiple) and share those views to the clients and put those views on the dashboard then the dashboard would automatically update depending on who's looking at it.

If it's for internal viewing you could do the same with standard reports filtered by current user as long as the current user's name was on the same row(s) as the data. Example: If clients data is managed internally by a PM, put a hidden helper column with that PMs name on all rows, then make your reports for data, charts etc.

But if you're doing a dashboard that you want to change on the fly from Client A to Client B etc. then you're on the right track but I don't think you need dynamic view. I've done this by building a metric sheet that drives all the metrics, charts etc. based on one criteria, "Client name" for example… then in other helper columns I build a user form with a drop down of those client names. Make the single criteria of "Client Name" pull from the most recent entry row of the form column(s). Put that form on the dashboard. Then the user could select a different client name, hit submit. Wait a few seconds for the data to update on the metrics sheet, then refresh the dashboard and it would be updated with the changed client name and all the formulas that reference it updating all the metrics/charts. You also can do this on row reports from that same form entry but that's also a bit tricky and takes a little longer for it to update.

Michele Rosenberg

Thank you Matt. I am looking for formula help to pull my client name from my master when matched with the form entry.

Paul Newcome

You say that new names to filter by are submitted by form. Are the new form entries at the top of the sheet or the bottom of the sheet?

Michele Rosenberg

They are currently at the bottom.

Trying to match form entry captured in

AND Client Name on Master

And pull information into:

I also need to at the point of client name match, need to pull in several additional pieces of information. I know that it is possible, I watched a gentleman do it but he skipped this formula :)

Also, I am working ahead, until I get my formulas to work to pull data and cannot get it to look like his and I am not sure what I did.

This is the one from the video:

This is mine:

https://www.youtube.com/watch?v=dmO5alE_oP4

Andrée Starå

https://community.smartsheet.com/discussion/129526/vlookup-index-return-client-name-from-master

Hi,

I hope you're well and safe!

Have a look at this template.

https://www.smartsheet.com/template-gallery/dynamic-dashboard-form-filter

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Paul Newcome

@Michele Rosenberg The formula to pull the most recent form entry when the new entries are at the bottom of the sheet would be:

=INDEX(COLLECT({Form Sheet Name Column}, {Form Sheet Name Column}, @cell <> ""), COUNTIFS({Form Sheet Name Column}, @cell <> ""))

Michele Rosenberg

Thanks Andre'e this isn't helping me understand what I did wrong in my formulas to pull data only. We aren't calculating any metrics. Just need to match form entry to master record and then pull back specific data for that client.

Paul Newcome

@Michele Rosenberg Did you take a look at my suggested formula?

Michele Rosenberg

Hello Paul! I did not see that :). Thank you for calling my attention to it! However looking at it, still isn't quite what I am looking for.

I am not doing a Countif. I have a form where a client name will be entered and logged on Client Query Form sheet. Then I want to take the latest entry on Client Query Form sheet and MATCH Client Name to Client Name on Client Master sheet and POPULATE the matched name in Client Look Up sheet (along with additional information on the client).

Paul Newcome

Right. The COUNTIFS is only a part of the overall formula.

COLLECT pulls together all entries into a list.

INDEX allows us to specify which one from that list.

COUNTIFS tells us to pull the last one from the list.

If you plug the formula into your sheet (and create cross sheet references as needed), you will see that the formula I provided does in fact pull the name from the last form entry.

You can either leave this separately in a cell or nest it in other formulas as needed.

Michele Rosenberg

Thank you Paul. I will attempt to update this formula and see how it works.

I appreciate all the feedback trying to help me!

Michele Rosenberg

Holy cow! @Paul Newcome THAT WORKED! Couple questions, how do I ensure the client is contained in the master? I was thinking I would take the last entry and match it to the master file names and then if it matched, populate in the look up field. I see that it is transferring the latest entry in the form, to the look up field but we aren't making sure it is on the master list.

Also, Based on the name matching, I then need to pull over some fields for that client and display in dashboard. Ideas?

Michele Rosenberg

Hello @Paul Newcome I posted a reply but it appears there was an issue when it was posting so going to try again.

The formula worked, however it is simply bring the information from the form and placing it in my Lookup sheet. I need it to match with a valid client on my master sheet so I need to do some kind of matching.

Also, trying to pull the rest of the specific client data into the lookup sheet to display on dashboard.

Paul Newcome

You can leverage this in a couple of different ways. You can drop that formula into a cell or sheet summary field and reference that within your other formulas.

=COUNTIFS({Range To Count}, @cell = [Sheet Summary Field]#)

Or you you can nest the formula in other formulas.

=COUNTIFS({Range To Count}, @cell = INDEX(COLLECT(…………..)))

Michele Rosenberg

@Paul Newcome are you willing to email me? michele.rosenberg@medimpact.com