horizontal lookup
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: horizontal lookup

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Columbia, SC
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have tried to do a horizontal lookup using multiple rows. This doesn't work. I can get it to work using a single row but not multiple. Any words of wisdom on how to get multiple rows to work?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    pipemaker - can you post an example of your formula and table your are looking up

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Columbia, SC
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Heres a rough example

    4/14 4/15 4/16 4/17 4/18 4/19

    23 28 32 37 41 45
    24 29 33 38 42 46
    25 30 34 39 43 47
    26 31 35 40 44
    27 36



    Something like the above. trying to do a horizontal lookup of the number and trying to get a return value of date. The array will not be sorted or anyhing like that. Not sure of the formula, just the basic hlookup wizard one.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-14 20:46, pipemaker wrote:
    Heres a rough example

    4/14 4/15 4/16 4/17 4/18 4/19

    23 28 32 37 41 45
    24 29 33 38 42 46
    25 30 34 39 43 47
    26 31 35 40 44
    27 36



    Something like the above. trying to do a horizontal lookup of the number and trying to get a return value of date. The array will not be sorted or anyhing like that. Not sure of the formula, just the basic hlookup wizard one.
    hlookup doesn't work that way - the way your table is setup you would look up a date and search the array for a specific row number to return a value. Not sure of how to get around this one just now.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There are different ways for lookups..

    hlookup, vlookup, offset, index - match,
    lookup,, and you can ofcourse have your own lookup functions.

    to your problem you can use offset or index-match function.

    you can see how i have use some of this functions in my file no 24.

    http://www.pexcel.com/download.htm

    well i have not tried using dates . but it should work.

    ni****h desai
    http://www.pexcel.com

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,785
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-04-14 20:46, pipemaker wrote:
    Heres a rough example

    4/14 4/15 4/16 4/17 4/18 4/19

    23 28 32 37 41 45
    24 29 33 38 42 46
    25 30 34 39 43 47
    26 31 35 40 44
    27 36

    Something like the above. trying to do a horizontal lookup of the number and trying to get a return value of date. The array will not be sorted or anyhing like that. Not sure of the formula, just the basic hlookup wizard one.
    I'll assume that A1:F6 houses your example sample with A1:F1 holding the dates.

    The problem statement. Given a number, retrieve the date that is in the same column as the number.

    In H1 enter: 29 [ a target number ]

    In I1 enter:

    =IF(COUNTIF(A2:F6,H1)=1,INDEX(A1:F1,SUMPRODUCT((A2:F6=H1)*COLUMN(A2:F6))),"Either Absent Or Too Many")

    As can be seen, this formula returns the text Either Absent Or Too Many if there are 0 or more than 1 occurrences of the target number in the relevant range.



    [ This Message was edited by: Aladin Akyurek on 2002-04-14 23:41 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com