Multiple Ifs Problem

[noelchb]

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.

[Tony Valko]

See if this helps:

http://contextures.com/xlFunctions02.html

[etaf]

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

[noelchb]

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

[etaf]

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

[noelchb]

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

thanks again for your help.

[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

[martindwilson]

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

[noelchb]

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.

[noelchb]

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

[martindwilson]

they may affect any lookup if you need to round them use a formula formatting wont work
for example 537.798658 rounds to 538 if you tried to match 538 with that it wouldnt

[Marcol]

Maybe this?

Please Login or Register  to view this content.

[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,"")

[kvsrinivasamurthy]

in B11 use

Please Login or Register  to view this content.

In B17 Use

Please Login or Register  to view this content.

[etaf]

@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

[noelchb]

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.

[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

[noelchb]

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:

Please Login or Register  to view this content.

[etaf]

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

[noelchb]

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

[etaf]

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

[kvsrinivasamurthy]

Pl see the attached file.

Please Login or Register  to view this content.