+ Reply to Thread
Results 1 to 8 of 8

Compare values in two different columns

  1. #1
    Registered User
    Join Date
    12-07-2010
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2003
    Posts
    21

    Compare values in two different columns

    Hello,

    I need to compare values in 2 columns within a range of data in two other columns. (Note: first 2 columns must be locked so data in first column is next to the associated value in next column. This also applies to the 2 columns I am compare it to - Column A & B locked with each other as well as D & E locked with each other).

    Desired outcome: Column C (Results) indicates"Missed visit" if values in A&B do not occur in D&E. "Good" if they occur.
    Bonus: "Late" if date in Column E do not occur in same month.

    Thanks for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Compare values in two different columns

    Hi sunijayanna,

    Try using below formula:-

    =IF(ISERROR(VLOOKUP(A:A,D:E,2,0)=B2),"Missed visit",IF(VLOOKUP(A:A,D:E,2,0)=B2,"Good","Late"))

    see attached:- Excel worksheet.xls


    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    12-07-2010
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Compare values in two different columns

    Thanks DILIPandey! That worked great.

    What part of the formula did it calculate that the visit did not occur in the same month?

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Compare values in two different columns

    You are welcome suniljayanna,

    IsError is checking if the visit happened or not.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    12-07-2010
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Compare values in two different columns

    How would I set parameters if visit is outside a X number of days or in a different month?

    Thanks

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Compare values in two different columns

    Hi Suniljayanna,

    This is what I was expecting

    I have revised the formula to handle all three cases :-
    Missed visit
    Met as Projected
    Late visit

    See formula in blue if you want to consider month of visit and change that accordingly to suit your requirement

    Excel worksheet.xls

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  7. #7
    Registered User
    Join Date
    12-07-2010
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Compare values in two different columns

    Perfect - thanks!

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Compare values in two different columns

    you are welcome . .

    cheers.

    Regards,
    DILIPandey

    <click on below star if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1