1

I have an excel file and need the following.
There are two sheets in one Excel file. The first sheet shows date and time values in column C & D. The second sheet does this too in column A & B.

I want to match up the date and time from sheet 1 with sheet 2 and when there is a match put column C (date) of sheet 2 in column G of sheet 1.

For example in sheet 1 cell C3 & D3 are "2/24/2009" & "15:00". In sheet 2 that matches with A13 & B13 and so we take C13 from sheet 2 and place it in sheet 1 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.

4
  • What is wrong with VLOOKUP? Feb 10, 2013 at 0:33
  • i have used excel for simple purposes before... i have no idea on how to do it...and I know nothing about Vlookup
    – obelisk
    Feb 10, 2013 at 0:36
  • Is there an error in your description? You say "we take C13 from sheet 2". But this isn't your date column. This is in column A of sheet 2, or not? Anyway, with the solution below you could retrieve any column. So edit just the index number.
    – nixda
    Feb 10, 2013 at 2:43
  • no it is not an error....there is one another column in sheet 2 (it is column 'C' ) that has some values corresponding to columns A & B.... for ex... A B C (date) (time) (views) so i need to transfer or import values from the cells of this "C" column to those rows in sheet 1, that have same values in columns "A" & "B" asin sheet 2.... and i want that new column in sheet 1 to be "G"
    – obelisk
    Feb 10, 2013 at 3:25

2 Answers 2

2

This can be done without changing the original data layout or joining any data. You can use an Index/Match combo like this:

=IFERROR(INDEX(Sheet2!$C$1:$C$10,MATCH(Sheet1!C2+Sheet1!D2,INDEX(Sheet2!$A$1:$A$10+Sheet2!$B$1:$B$10,0),0)),"")

enter image description here

1

You could use =VLOOKUP together with two helper columns.

Syntax: =VLOOKUP( value, table_array, index_number, [not_exact_match] )

Since VLOOKUP can only compare a single column you need to join your two columns date and time in a single helper column:

  1. In sheet 1 place this in column F and fill it down : =C2 & "-" & D2.
    This creates our helper column as you can see in the screenshot.
    Don't worry about the wrong time format at the end.

    enter image description here

  2. In sheet 2 you have to insert a new empty column A so your date and time columns shifts one column to the right.

    Its important for VLOOKUP that the value to search is in the first column of your search table in sheet 2. So this time we use column A in sheet 2 to create our helper column.

    Place this in your empty column A of sheet 2 and fill it down : =B3 & "-" & C3 again.

  3. Now we are ready to use VLOOKUP. Insert this formula in sheet 1 column G and fill it down.
    =VLOOKUP(F2,sheet2!$A$2:$D$4,4,FALSE)

    This will search the value from sheet 1 cell F2 in the first column of sheet 2 column A.
    After a match, it returns the value of your desired column from your sheet 2 table. In your example it should be column D (remember the shift)

    enter image description here

Additional cosmetic improvements

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .