I'm working on a spreadsheet that I want to use in conjunction with an Active Directory Export. Thus far I've got the export working as I'd expect, but I'm having issues with getting my formula to work right.

Let me set the stage for what I'm trying to achieve.

I'm currently trying to do an intersecting graph. So down the left side I have domain group names, and along the top I have user names. I simply want to put an X in the fields where the user is in the group represented at that intersection. (look at PUALBERTDO and the Domain Users Group. There should be an X there because he is in that group.)

The export of the Users and groups list is in another tab named ad_user_groups.

Advertisement

The format is with the username in column A, the group in column B, and the X in column C.

My google-fu returned the following formula (that I modified to use in this spreadsheet):

=VLOOKUP(B1,IF(ad_user_groups!B2:B7701=A10,ad_user_groups!A2:A7701,""), 3, FALSE)

Advertisement

What this invokes is the VLookUp function, which is a fairly dumb logical lookup function that searches when the lookup fields are in ABC order.

VLOOKUP syntax is like this: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Advertisement

It first looks at the subject which it is looking up (B1 = PUALBERTDO in this case).

Then it is told where to look which I pass an If Statement:

IF(ad_user_groups!B2:B7701=A10,ad_user_groups!A2:A7701,"")

Syntax =IF(logical_test, [Value_if_true], [Value_if_false])

Advertisement

So in the IF statement I'm telling it to look between B2:B7701 on the ad_user_groups page for the value that is in cell A10.

If that returns true, then it returns the name that VLookup can use (PUALBERTDO)

If False, it returns a blank that VLookup will skip to the next qualifying entry.

Advertisement

for VLOOKUP, if the result is true, it returns the contents of column C (or column 3 as written in the formula), which in my case is the X.

The FALSE in VLOOKUP is instructions to not accept anything but an exact match. If it were true, Domain User would be a match when I am searching for Domain Users. (Note the 'S' on users.)

Advertisement

So, long story short. My formula doesn't work. Instead I get a #Value mark. I'm not sure what I'm doing wrong and why it won't return the X it should in the Domain Users field, and leave the group above blank.

Any Excel aficionados here?