Complex IF Functions

[Berlinetta]

Forgive me if this has been asked before...
I'm running an environment where I'm running multiple conditions.
Basically, it's for a commission form.
Say this is how the column looks.
2600 Goal 1
3400 Goal 2
4200 Goal 3
5000 Goal 4
5800 Goal 5
...
**** This is where the Goal 1 would show up
4100 This is the actual income.

Ideally, what I want to do is get the *'d field to return "Goal 3", a
this is the next tier being pursued for.

I'd imagine the best way to do this would be to do a complex I
function, but no matter how I enter it, it comes up with the wron
results.

Can anyone suggest a way of doing this?

Thanks

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(B1:B10,MATCH(TRUE,A1:A10>=4100,0))

 

[Myrna Larson]

Assuming the 4100 is in A3, and your table in A1:B5,

=INDEX($B$1:$B$5,MATCH(A8,$A$1:$A$5)+1)

 

[Frank Kabel]

Hi Myrna
I think this will produce the wrong result for an exact match )
e.g. for A8=5000 this would return 'Goal 5' instead of 'Goal 4'

But this depends of course what the OP expects ;-)

 

[Myrna Larson]

I think this will produce the wrong result for an exact match )
e.g. for A8=5000 this would return 'Goal 5' instead of 'Goal 4'

But this depends of course what the OP expects ;-)