Multiple Ifs Problem

Hi,

I'm hoping somebody can help me with the attached; I've been trying all day without success!!

I have a figure in Cell A3.
It is compared against 5 different figures.
Depending on the criteria it matches, it will result in 5 possible figures.

The attached should explain it better.

Thanks in advance for any help.

to lookup the nearest highest value with vlookup (the usual is the lowest or the exact)
you can use this
=VLOOKUP(SMALL(D3:D7,COUNTIF(D3:D7,"<"&A3)+1),D3:G7,4)

which will return the result you need

the 2nd IF
=IF(A3<D3,G3*-1,"")

see attached

Hi,

Thanks for your replies so far - i've tried implementing the suggestions but i'm still struggling (sorry).

I've attached the file again with some minor modifications and hopefully it will make things easier to solve.

Thanks again, i really appreciate the help.

Attachment:
ifs.xls

do you need the layout as described as that is a lot different and will not work with the vlookup i posted

yes the new layout is the one i want. i was hoping to adapt your formula but was unsuccessful.

thanks again for your help.

no you cant adapt Vlookup very easily

you will need to use an index match with a -1 option, but that needs the data sorted descending to work
then you have target in one table and then band in the 2nd - so it would then have to extract just the letter to be able lookup on the 2nd table
and if its just an example any slight changes are going to cause an issue

I think to do this will probably need a macro - not my area

is the layout and terms exactly as you would be using them in the production version of excel

Originally Posted by etaf

no you cant adapt Vlookup very easily

you will need to use an index match with a -1 option, but that needs the data sorted descending to work
then you have target in one table and then band in the 2nd - so it would then have to extract just the letter to be able lookup on the 2nd table
and if its just an example any slight changes are going to cause an issue

I think to do this will probably need a macro - not my area

is the layout and terms exactly as you would be using them in the production version of excel

yes the layout is the final production version. i appreciate taking the time to look into this; I'll keep trying.

Originally Posted by martindwilson

these are the actual values in col c is that correct?
275.352913
344.191141
430.238927
537.798658
672.248323

Yes those are correct. they are copied from a different source so i have them rounded to the nearest whole number

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.

in B11 use

=IF(ISERROR(INDEX($G$3:$G$7,MATCH(A3,$D$3:$D$7,1)+1)),"",INDEX($G$3:$G$7,MATCH(A3,$D$3:$D$7,1)+1))

In B17 Use

=IF(A3<D3,G3*-1,"")

@kvsrinivasamurthy the OP has changed the layout of the table in post #4
so they are now two separate tables without a common link apart from part of the text

simple enough , just add an if at the front

=IF(C2>C9,C15,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 still use
control+shift+enter

Originally Posted by etaf

simple enough , just add an if at the front

=IF(C2>C9,C15,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 still use
control+shift+enter

that's perfect; thanks a million.

one last thing, the Code in D22 below should change from:

=RIGHT(INDEX(B5:B9,MATCH(MIN(IF(C5:C9-C2>=0,C5:C9,FALSE)),IF(C5:C9-C2>=0,C5:C9,FALSE),0)),1)

d22 and d23 , was me playing and testing - you can ignore those

fair enough @etaf, thanks a million for your help - i really appreciate it.

your welcome
new to the forum, but if you click on the * that adds to my reputation

Pl see the attached file.

=index($c$11:$c$15,match(right(index($b$5:$b$9,match($c$2,$c$5:$c$9,1)+1),1),right($b$11:$b$15,1),0))