Index? Match? Range?

[loosenut]

Hello! I am trying to build a formula which references a numerical number in a cell (BB24) of $1,500,000, and if BB24 is between the low and and high number in rows 2-5, it yields the entry in the same row in column RC. I thought I had it with the following array formula, but it yielded the default value of 5% rather than 35% which is what I was hoping for. Any suggestions would be welcome - thanks!!

={IF(AND(BB24>BA2:BA5,BB24<BB2:BB5),BC2:BC5,5%)}

BA	BB	BC
Low	High	RR
0	$1,000,000	50%
$1,000,000	$2,000,000	35%
$2,000,000	$2,500,000	25%
$2,500,000	$3,000,000	15%

 

[jasonb75]

Using IF as an array is meaningless if the formula is not wrapped with a useful function.
Additionally, AND never returns an array result, it returns TRUE if every cell in the array meets the criteria, or FALSE if one or more do not meet the criteria.

The easiest way would be to enter $3,000,000 into BA6 then use
=LOOKUP(BB24,BA2:BA5,BC2:BC5)

Note that there is inconsistency in your table, should an exact amount of $1,000,000 be classed as 35% Low or 50% High? It can only be one or the other. In your table it is both, in your formula it is neither.

If $1,000,000 should be 35% and $999,999.99 should be 50% then the table is fine as it stands. If it should be 50% for $1,000,000 and 35% for $1,000,000.01 then you need to add .01 to each of the figures in BA3:BA5 (BA2 should still be 0).

 

[loosenut]

Jason,

Many thanks for your answer and the advice. It worked like a charm! Add another solution to your tally!

Thanks again.....