+ Reply to Thread
Results 1 to 4 of 4

Selecting values from adjacent rows

  1. #1
    Registered User
    Join Date
    10-14-2009
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Selecting values from adjacent rows

    Hi,

    I have 2 rows:
    Row1: each column has the date (e.g. dd/mm)
    Row2: each column has a numeric value e.g. 272)

    I want to in another cell do the following.

    a. Search Row1 to find a date equal to todays date.
    b. Then select the matching value from Row2 and place that in my cell.
    or if it cant find todays date then it should take the value from the previous cell (date).

    ie. Row1: 04/11 05/11 06/11 07/11 09/11
    Row2: 345 340 335 320 305

    If todays 06/11 then it must return 335 to my cell. If today is 08/11 it must return 320 into my cell.

    Please assist with this comulation.

    Thanks
    Last edited by illinge; 11-05-2009 at 06:37 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Selecting values from adjacent rows

    You could use HLOOKUP

    =HLOOKUP(TODAY(),1:2,2)

    or INDEX/MATCH

    =INDEX(2:2,MATCH(TODAY(),1:1))

  3. #3
    Registered User
    Join Date
    10-14-2009
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Selecting values from adjacent rows

    Thanks, but I am still struggling:

    I am getting the info from a different worksheet.

    The info I need are in WS1 and they are laid out as follows:
    WS1: Row1 contains all the values e.g. 356, 674, 893,......
    WS1: Row5 contains the dates e.g. 03/11, 04/11, 05/11.....

    My command in WS2 is : =HLOOKUP(TODAY(),'WS1'!F5:R5,1,FALSE)

    It brings back a wird number. My cell in WS2 is formated for a number

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Selecting values from adjacent rows

    If you look at my HLOOKUP you will note I have a 2 row table_array (1:2), the first row (1) being that containing the dates (in which the criteria value will be searched for) and the second (2) being that containing the values of interest... the row_index_num (to determine from which row of the table_array the result should be extracted) is set to be 2, ie return the 2nd row of the range from the column in which the relevant date is found in the header row.

    In your version of the same the HLOOKUP table_array consists of only one row of data namely the dates thus all that is being returned by the HLOOKUP is the date value itself which leads me on to...

    It brings back a wird number
    Dates in XL are integers, the number you're seeing is the Integer value of the date value returned from F5:R5.

    An HLOOKUP as it turns out - will not work here anyway given it can only look downwards (in the same way a VLOOKUP can only look to the right), so you will need to revert to the INDEX match approach also illustrated in post # 2
    (changing reference to row 2 to be F1:R1 and references to row 1 to be F5:R5)
    Last edited by DonkeyOte; 11-05-2009 at 10:42 AM. Reason: re-read last post and noticed results above criteria

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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