Need help with a VLOOKUP formula

jahmeanne

Hi all,

Just to be upfront, formulas are my Achilles heel, so I'm not sure if I'm doing this right. My company has a Lucidchart training coming up, and we've created a sheet/form that will list out everybody who has filled out the form and has expressed interest in the training. I've also imported another sheet that lists all of the users who currently have access to Lucidcharts. This list also includes a column that indicates whether they are a licensed user or not.

I was able to create the following VLOOKUP that will inform me if the user signing up for the training already has a Lucidchart account. Basically if the user signs up for training using our form and their email account exists in the Lucidchart user list, it will populate under the column "Has software account".

=VLOOKUP(Email1, {LucidChart Users_Jan2023 Range 1}, 1, false)

There is another column on our sheet called "Is licensed". I need to see if there's a way to reference the Lucidchart user list sheet I imported to indicate whether the user in question has a license. I tried the following formula but am obviously not doing this right.

VLOOKUP(Email1, {LucidChart Users_Jan2023 Range 1}, 1, false, {LucidChart Users_Jan2023 License}, 1, false)

Any help you can provide is appreciated. Thanks a bunch!

Paul Newcome

I personally would use a COUNTIFS to see how many times that person's email shows up with the box being checked. If that COUNTIFS result is zero, then they are not licensed, otherwise they are.

=IF(COUNTIFS({Email Column}, @cell = Email@row, {Licensed Column}, @cell = 1) = 0, "", "Is Licensed")

jahmeanne

Hi Paul,

Thanks for reaching out! unfortunately, there are no check boxes associated with these sheets. Here's an example of what I'm looking for:

I exported a user list directly from LucidChart. It displays everybody who has an account as well as who is licensed. I used myself as an example as I have both an account and a license allotted:

This is sheet / form we will be using to see who has signed up for the trainings. The first column, Email, is a "Created By" column and will populate the users information as soon as the fill out the form. I need to setup VLOOKUPS for the "Lucidchart Account" and "Has a license" that will point back to the LucidChart users list. The behavior I'm looking for is that the formula will look at the Email (Created By) column and then reference the user list sheet to see if they have an account and if they have a license. If they have an account, that will then populate under the "Lucidchart Account" column, otherwise it would false. If they have a license, that would also populate under the "Has a license" column, else be false.

Paul Newcome

In that case you would remove the licensing portion from the COUNTIFS to find out if they have an account, and then for the licensing bit you would change the 1 to "yes".