VLOOKUP or INDEX(COLLECT?

jb@59069

Hello

This seems simple reaching Excedrin point.

We have a sheet, "Client Project", that we use as a template for each client. When a new client comes on board, I use "Save as New" into their Workspace. A few quick changes and it's ready for form entries to begin flowing. However, I am trying to figure out how to automate those few things to reduce oversight or human error.

Disclaimer: The images shown are a representative of our real sheets.

I have a sheet called "Client List" that our CEO populates via a form that captures all pertinent data, this holds the data I would like to populate to the Client Project sheet with.

See the images below as reference.

I am attempting to get Client Project, Client1 and CID1 to grab data from Client List, Clients and Identifier respectively. I only need the first cell of Client Project to populate. Even if I have to manually enter the client name into Client Project, Client1 cell, if that could then trigger CID to populate from the corresponding value from Client List.

Thanks for any help.

ClientList.JPG

ClientProject.JPG

Genevieve P.

Hi @jb@59069

An INDEX(MATCH should work for what you're looking to do! The structure of an INDEX(MATCH is like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}))

So in your instance, you'd want this in your CID column in the second sheet:

=INDEX({Source Sheet CID column}, MATCH(Client@row, {Source Sheet Clients Column}))

You would just need to add in the Client name into the Client column as you suggested, then the CID should populate with the relevant information. You can use this to populate any other items in the same row as that client as well, just adjust the {Column with value to return} at the beginning of the formula to reference what you want to pull.

Let me know if this works for you!

Cheers,

Genevieve