Need a slick answer please to a simple query

[RET79]

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

[Mark W.]

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 ]

[Rocky E]

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...

[Mark W.]

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.

[Dave Patton]

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))

[Mark W.]

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