Multiple Ifs Problem

Originally Posted by etaf

as you are looking for the highest , nearest value

this as an array formula will pull the first bit of info from table 1
=INDEX(B5:B9,MATCH(MIN(IF(C5:C9-C2>=0,C5:C9,FALSE)),IF(C5:C9-C2>=0,C5:C9,FALSE),0))
and enter as an array formula

returns target D for your example

now you need to look up band D to find the value

to do that , we need to strip the last character from target D to get D
and then lookup the BAND ? , so we also need to strip off Band to get the last character to search for D in that table

So we return the highest nearest value using the above array - and then we get the last character , as that is the only bit common on both tables , using Right()

then we use index match again , but have to look at just the last character in the band column using right() again to lookup the adjacent column to get a value

so we get target D in your example and then right() to get the D
then we lookup for the last character being D in the 2nd table

but any changes in the tables or the names used , will, stop the formula from working at all

so put this into C17

=INDEX(C11:C15,MATCH(RIGHT(INDEX(B5:B9,MATCH(MIN(IF(C5:C9-C2>=0,C5:C9,FALSE)),IF(C5:C9-C2>=0,C5:C9,FALSE),0)),1),RIGHT(B11:B15,1),0))
and then use an array
todo that- you use the control + shift + enter keys - so you get {} around the formula

see attached spreadsheet with the result using the above formula

for the -ve value

=IF(C2<C5,C11*-1,"")

Many thanks @etaf, this is exactly what i want. except i did forget to say if the Total figure exceeds the Band E target, then the desired result is also Band E. At the moment it displays #N/A but that's my fault for not explaining it fully.

For example, if C2 equals 700, then C17 should show the Band E price.

Sorry for omitting this detail; i hope you can update the formula without too much trouble.

Thanks again.