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

Thread: Need a slick answer please to a simple query

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi.

    This time I table like this

    2002 2003 2004 2005 2006

    $32,000 $45,000 $65000 $100,000 $1,000,000

    This table shows my expected earnings when I master excel

    In this table, the years are fixed. However, the amounts are determined by formulas and change when you change the inputs in some other cells not in that table.

    However, this is what I need.

    I need a quick way of getting a cell to display the year when the earnings have exceeded $50,000 say.

    In this example, the formula would give us the year 2004.

    I know someone out there knows an elegant solution, probably to do with index etc.

    Thanks guys,

    RET79

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    With your table in cells A1:E2, use...

    =LOOKUP(50000,A2:E2,A1:E1)+1

    [ This Message was edited by: Mark W. on 2002-04-08 10:42 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

    Range_lookup is the key. "Range_lookup is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned."

    The apptoximate match is very handy for just the problem you describe. I use it to look up prices for varying quantities. each column is the top of the price bracket. For example 1 to 499 is 499, etc. Appears to be fast. The list must be sortted and bound by the highest value you expect or an N/A can result.

    HTH

    Rocky...

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 11:20, Rocky E wrote:
    HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
    HLOOKUP searches the 1st row of the table_array for the lookup_value. In this case the 2nd row needs to be searched; thus HLOOKUP can't be used.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,484
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default



    Mark W posted a Lookup solution.
    The following is an enhancement that covers the possiblity that the lookup value is one of the values in the lookup array.

    One version uses a cell reference and the other the specified value.

    IF(COUNTIF(A2:E2,A5),LOOKUP(A5,A2:E2,A1:E1),LOOKUP(A5,A2:E2,A1:E1+1))

    =IF(COUNTIF(A2:E2,50000),LOOKUP(50000,A2:E2,A1:E1),LOOKUP(50000,A2:E2,A1:E1+1))

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 20:19, Dave Patton wrote:


    Mark W posted a Lookup solution.
    The following is an enhancement that covers the possiblity that the lookup value is one of the values in the lookup array.
    The original request was to identify the year in which the salary EXCEEDED a given value. Hence...

    =LOOKUP(50000,A2:E2,A1:E1)+1

Some videos you may like

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
  •