[ITstudent2006]

All,

I am in the middle of a project of comparing VPN users (via AD Group SSLVPNUsers) with users in a certain company group. Essentially, management wants to know who, if any in this group have VPN access. Now, I have done this a million times in the past for various managers and groups but I seem to have screwed up my templated formula.

I am not the greatest at Excel, quite frankly I hate it. I would however like some help with this formula. Scenario below:

Column A (starts at A2) = List of users in this specific group (441 users)
Column B (starts at B2) = List of ALL users in the AD Group SSLVPNUsers (43,596 users)

I want a formula that looks at Column A to see if its in Column B, if so then its True (in column C). If not, then its false (in column C).

I tried explaining it the best I can. Hopefully you understand. My current formula is below:

=IF(ISNA(MATCH(A2:A441-B2:B43596,Search,0)),FALSE,TRUE)

[nrjoyner]

Try this formula:

=IF(ISNA(VLOOKUP(B2,$A$2:$A$442,1,FALSE)),"FALSE", "TRUE")

Paste it in C2 and then copy down to the end of your data. You may need to adjust the range ($A$2:$A$442) to ensure that all the data in column A is captured. I usually use a named range instead of specifying the range for column A. If you want to specify the whole column instead of where you have data today, you could replace the range with a column reference ($A:$A).

The VLOOKUP function works great, but it can get buggy if you have different cell formats like text vs. general or number.

[ITstudent2006]

As soon as I am done with my current duty I will attempt that formula. My question is should the B2 be a range from B2-B43596? I want to see if users in A2-A441 are listed in the B2-B43596 list.

[nrjoyner]

My question is should the B2 be a range from B2-B43596? I want to see if users in A2-A441 are listed in the B2-B43596 list.

If you copy the formula to C2 and then copy the formula down, the B2 will change to B3, B4, etc.

VLOOKUP does an evaluation. It will look at 1 cell and then review the data range for the evaluation. In this case, it will evaluate cell B2 and look for that value in the range for column A. When you copy the formula from C2 to C3, the formula will say B3. It will then do the evaluation for cell B3. Copy it all the way down to C43596 and you will get a True or False in each cell in column C that shows if the column B value exists in column A.

[ITstudent2006]

Ok. I couldn't get it to work. I had an employee give me a spreadhseet he had that had a formula built in.

Thanks for the help anyway. I did learn something. :)

[JBeaucaire]

Another suggested starting formula for C2, then copied down:

=ISNUMBER(MATCH(A2, B:B, 0))

That should provide a list of TRUE/FALSE results as the cell is copied downward.