Can you help me with organizing data in Microsoft Excel?

Sachin Kanchan February 10, 2013
Pinterest Stumbleupon Whatsapp
Ads by Google

I have an Excel file and need the following. There are two tabs (sheets) in one Excel file, the first tab in column C & D show date and time in sheet 2 column A & B show date and time. I want to match up the date and time from sheet 1 (C & D) with sheet two (A & B) and when there is a match, put column C of sheet 2 in column G of sheet 1.

For example Cell C3 & D3 are “2/24/2009” & “15:00” in sheet two that matches with A13 & B13 and so we take C13 from sheet 2 and place it in G3.

I want to be able to replace the cells in sheet 1 and sheet 2 and they should be able to repopulate with correct data.

I don’t know how it’s done. Urgent help needed.

  1. dragonmouth
    February 20, 2013 at 12:30 pm

    Post your question on Tek-Tips Forums, Excel section:
    http://www.tek-tips.com/

  2. Sohrab Kersi Gandhi
    February 13, 2013 at 4:55 am

    Hey,

    This should do the trick for you.

    =IF(VLOOKUP(Sheet1!$A$1:$A$100,Sheet2!$A$1:$C$100,3,FALSE),IF(VLOOKUP(Sheet1!$B$1:$B$100,Sheet2!$B$1:$C$100,2,FALSE),VLOOKUP(Sheet1!$B$1:$B$100,Sheet2!$B$1:$C$100,2,FALSE),"Time not matching"),"Date Not Matching")

    Understanding:
    1. The 1st IF condition is looking up the checking the DATE and the 2nd IF condition is checking the TIME.

    2. VLOOKUP(Sheet1!$A$1:$A$100,Sheet2!$A$1:$C$100,3,FALSE)

    Parameter 1 : Sheet1!$A$1:$A$100
    Search : This looks up the values represented by the area A1 to A100 of sheet 1

    Parameter 2 : Sheet2!$A$1:$C$100
    Source of data to match : Sheet 2 - A1 to C100.

    Parameter 3 : Column Index number
    If a match occurs, then that column whos index number is mentioned will be displayed

    Parameter 4 : FALSE
    Does a full match, no partials.

    Now, just nest the VLOOKUP into another IF for looking up the TIME field. If both match, then show the corresponding column, else show the corresponding error message.

    Hope this solves your issue.

  3. Paul Pruitt
    February 12, 2013 at 6:12 pm
  4. Bruce Epper
    February 12, 2013 at 5:23 pm

    The requirements of your solution depend on a couple of factors. On each of these tabs, are the date and time columns actually using the date and time formats of the date/time serials used internally by Excel or are they General or Text values in the cell? Also, if necessary, can an additional column be added to each tab to make the solution simpler (this column can be hidden from view and not show up in printed results)?

  5. Alan Wade
    February 10, 2013 at 1:58 pm

    The best way to achieve your desired results is to copy and paste the question in this forum: http://www.excelforum.com/
    These guys know Excel inside out.