Results 1 to 4 of 4

Thread: Lookup data

  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Unanswered: Lookup data

    Hello Guys,

    I wonder if you can help,

    In Excel - I have two spreadsheets. I need to copy the values from Sheet 2 column date to sheet 1 column date.


    Example:

    Sheet 1 Sheet 2
    col 1 | Date Col 1 | Date
    a 02/03/2010 b 01/01/2010
    b 01/01/2010 a 02/03/2010
    c 03/03/2010 c 03/03/2010

    Can any lookup functions achieve this? As you can see the data in Sheet 2 isnt sorted the same as sheet 1.

    Thanks

    Lucy

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Lucy,

    So long as there is only one row of each date then, yes, you can use VLOOKUP() (or a similar combination of functions) to pull the data over.

    How many rows and columns of data are there?

    Would you like me to attach an example?

  3. #3
    Join Date
    Nov 2003
    Location
    england
    Posts
    95
    Hello Colin,

    Each row contains a date or contain blanks. I thought of using VLOOKUP but it mentioned something about only using data that has been sorted in ascending order. The data I am trying to match is sorted differently on sheet 2 compared to sheet 1.

    Sheet 1

    col1 col2
    A37/890 | BRISTOL ROAD [Insert date data from sheet 2 (inc blanks) when col 1 sheet 1 matches col 1 sheet 2]
    A37/930 | BRISTOL ROAD [Insert date data from sheet 2 (inc blanks) when col 1 sheet 1 matches col 1 sheet 2]


    Sheet 2

    Col 1 Col 2
    A37/890 | BRISTOL ROAD 11/05/2004
    A37/930 | BRISTOL ROAD

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Lucy,

    When using VLOOKUP(), the optimal approach is to use [range_lookup] = 1 (or TRUE) but this requires the data to be in ascending order. If you pass in 0 (or FALSE) then it will find an exact match irrelevant of the order of the data - the trade off is that the lookup is a touch slower. A lot of people prefer to use INDEX() and MATCH() for this, but the formula is more complicated, so I think that you're best off using VLOOKUP() here.

    I've attached a simple example to show that it can be used when the data order is mixed up. The main restrictions with VLOOKUP() are that you can only lookup data to the right of the lookup column and that the lookup column index (eg. 2 for 2nd column) is a magic number: it won't adjust if you insert a column in the lookup table.
    Attached Files Attached Files

Posting Permissions

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