First post here:
I have a spreadsheet where first Column A lists all users that are required to attend a class and the second Column C-K lists informaion on all users that actaully did attend. Column B is blank.
I was given the below formula to use to do a compare so I could find out who was required but did not attend, however, the formula does not work. Just so I'm clear, Column C has the user name of the attendee, and columns D-K have misc. information that is not important to me. All I want to do is find out how many people listed in Column A (required attendees) are not also listed in Column C (actual attendees).
Here's the formula as it was sent to me:
=IF(ISERROR(VLOOKUP(A2|C:K|7|FALSE))|"Not Registered"|IF(VLOOKUP(A2|C:K|7|FALSE)=0|"Registered"|VLOOKUP(A2|C:K|7|FALSE)))
Thanks for any help you can provide,
Frank
Last edited by racerboy; 07-15-2008 at 11:13 AM.
assuming info starts in a2 put this in b2
and drag down=IF(ISERROR(VLOOKUP(A2,C:K,1,FALSE)),"Not Registered",IF(VLOOKUP(A2,C:K,1,FALSE),"Registered"))
you dont really need vlook up as you are only looking at one column but i just changed what you had so you can see how it works.
a simpler way is
=IF(ISERROR(MATCH(A2,C:C,0)),"not registered","registered")
Last edited by martindwilson; 07-15-2008 at 10:38 AM.
Hmm, well, those bars should be commas, but I'm not sure why you've got the range C:K in there, and pulling in values from column 7 if you really only want to look to see if a name is in column C.
How about a countif approach?
This looks for the value in A5 to see if it's in column C at all; if not there, you get "Not Registered", but if it finds the name anywhere in column C, you get "Registered".=IF(COUNTIF(A5,C:C)=0,"Not Registered","Registered")
try,
you can place the code any column, this will match anything in column A against anything in column C,
thanks reg=IF(INDIRECT(ADDRESS(ROW(),COLUMN(A:A)))="","",IF(COUNTIF(C:C,INDIRECT(ADDRESS(ROW(),COLUMN(A:A),4)))=0,INDIRECT(ADDRESS(ROW(),COLUMN(A:A),4)),""))
racerboy,
That is not really an acceptable title...
please read our forum rules (link in my signature) and amend the title according to rule # 1.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks for all the suggestions. I did some more research on the VLOOKUP function and the reason why the "7" was included in the original formula was because column 7 has the status (Attended) of the attendee.
I just did this simple formula:
=VLOOKUP(A2,C:K,7,FALSE)
and for every student that was required to attend and did, it put "Attended" (again, taken from column 7) in Columns C and for those students who were required (Column A) but did not attend (Coulmn C), it put N/A.
How can I get it to put "Not Attended" or if they registered for the class (again, this would be listed in column 7) to say "Registered"?
If a student took no action (they were told to register, but never did) there is no information on that student in any other column other than their name in column A.
Thanks again for all the help!
Frank
Last edited by racerboy; 07-15-2008 at 11:25 AM.
HTML Code:=IF(ISNA(VLOOKUP(A2,C:K,7,FALSE)),"not attended",VLOOKUP(A2,C:K,7,FALSE))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks