Need a slick answer please to a simple query

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
526
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 :wink:

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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top