Multi IF, Multi OR & Multi AND

Hi,

How can I write this formula:

IF(A1=1 and A2=2) then VLOOKUP(C1....) OR IF(B1=3 and B2=4) then VLOOKUP(D1....)

OR................ otherwise F1

Something like..

=IF(AND(A1=1,A2=2),VLOOKUP(C1,....),IF(AND(B1=3,B2=4,VLOOKUP(D1,.....),.......,F1)


But you don't want to many nested IF's, especially in pre 2007.

If you detail your problem, someone may be able to help create a more efficient formula.

Just a minor correction to NBVC's formula:

Please Login or Register  to view this content.

Cheers,

Hi,

Thanks for the help, well... I really need to use multiple IFs and ORs in XL 2003, below is my formula; can we shorten it somehow to be able to use more IFs:

=IF(AND($H$5="g",AK5=AB2),VLOOKUP(AB2,$AK$5:$AN$105,3,FALSE),IF(AND($H$9="g",AK5=AC2),VLOOKUP(AC2,$AK$5:$AN$105,3,FALSE),IF(AND($H$13="g",AK5=AD2),VLOOKUP(AD2,$AK$5:$AN$105,3,FALSE),IF(AND($H$17="g",AK5=AE2),VLOOKUP(AE2,$AK$5:$AN$105,3,FALSE),IF(AND($H$21="g",AK5=AF2),VLOOKUP(AF2,$AK$5:$AN$105,3,FALSE),IF(AND($H$25="g",AK5=AG2),VLOOKUP(AG2,$AK$5:$AN$105,3,FALSE),NA()))))))

Hi,
Is it possible to share a version of the spreadsheet you are using so we can visually see what you are trying to accomplish? I am not sure if you can shorten it down due to the lookup value changing based on each logical test. Also I found when searching that someone may have an answer already to your puzzle with the nested IF's, searched while I was replying here.
http://www.excelforum.com/excel-gene...e-formula.html

Basically he is suggesting using some clever calculations outside the nesting, but I am not sure that would help your situation.

Please Login or Register  to view this content.

Originally Posted by Ralem

Hi,

Thanks for the help, well... I really need to use multiple IFs and ORs in XL 2003, below is my formula; can we shorten it somehow to be able to use more IFs:

=IF(AND($H$5="g",AK5=AB2),VLOOKUP(AB2,$AK$5:$AN$105,3,FALSE),IF(AND($H$9="g",AK5=AC2),VLOOKUP(AC2,$AK$5:$AN$105,3,FALSE),IF(AND($H$13="g",AK5=AD2),VLOOKUP(AD2,$AK$5:$AN$105,3,FALSE),IF(AND($H$17="g",AK5=AE2),VLOOKUP(AE2,$AK$5:$AN$105,3,FALSE),IF(AND($H$21="g",AK5=AF2),VLOOKUP(AF2,$AK$5:$AN$105,3,FALSE),IF(AND($H$25="g",AK5=AG2),VLOOKUP(AG2,$AK$5:$AN$105,3,FALSE),NA()))))))

This formula doesn't really make a great deal of sense to me.

It basically seems to read that if "g" can be found in any of H5, H9, H13, H17, or H21 and AK5 can be found in AB2:AG2 then to conduct a VLOOKUP based on AK5.

However given the use of references etc the implication is simply that you wish to return the contents from Column AM - ie if AK5 is found in the header row then return the value in Col AM (given the VLOOKUP of AK5 will simply be the first row)

Based on the above, perhaps then:

Please Login or Register  to view this content.

if the "g" test is repeated on each row then I would suggest calculating just once and referring to the result cell thereafter (ie avoid repetitive calcs.)

Thanks guys,

but, DonkeyOte, It'snot like: IF any of OR, each IF(AND is one different case...

The formula provided apes the formula you posted so I'm not really sure as to your point.

Your current formula makes little sense from a syntax perspective given each VLOOKUP is identical albeit for the criteria which is itself simply the value in AK assuming it is found in row 2.

I would suggest you post a sample file to better illustrate your question / requirements.

Please find the attachment.

I agree with DonkeyOte, as to your formula not making too much sense.

You have the same code repeated in column L several times... I assume you just want to match the code on the same row?

To me, this formula: =IF(G5="g",N5,NA()) seems to be what you might want... I am not sure why the Vlookup is even necessary

If says that if G5 is "g", then just return what is in column N within the same row.. (i.e. the 3rd column from column L.

Hi,

Please find the attachment, it's the same with some corrections....

Have you clicked F5 and L5, this is designed to auto calculate Slope by entering numbers 1,2,....or 5 over column D, try to delete and add some of these numbers and notice the calculated slope....
did you notice that I6 and I7 are N/A coze there is no g in cell G13;

Slope in cell F5 is calculated from 1 numbers typed in column D... g in cell G5 is related to slope1 data and will show in column I,J and K the data only opposite those ones.... hope this clarify it..
Actually this is just a part of my spreadsheet, the idea is to apply conditional format on data series on a chart and have to do this coze I'm using excel 2003.

Maybe in I5, then:

Please Login or Register  to view this content.

copied down.

You can have up to 30 AND() conditions in there (in Excel2003).

use same in other columns.. but adjust the N5 to get your desired number from M:O

Very nice NBVC, thanks a lot

this will solve for me the limitation of IFs in XL 2003

Originally Posted by Ralem

this will solve for me the limitation of IFs in XL 2003

Not sure if that is a question or not, but yes, it will solve that limitation as you can use up to 30 AND() conditiions within the OR() functions