IF formula

**stait** · 10-03-2011, 04:17 PM

I am trying to create an IF formula which will be dependand on a number of variables. I have attached a sample of what I am doing. In Q3, I want it to return based on a number of potential conditions:

if N5 if greater than 0 and A5 if less than 32, ..... the Lookup shown
If N5 is greater than 0 and A5 is greater than 31, ..... the Lookup shown
(the above is working - I want to add the blwo conditions as well)

if L5 is greater than A5 and N5 if greater than 0 and A5 if less than 32, then same above look up however, instead of looking up

"O3/(LOOKUP(A3,'T&O Pay Scale - 322 - Admin'!$A$3:$A$23,'T&O Pay Scale - 322 - Admin'!$C$3:$C$23)),IF(AND(M3>0,A3>31),O3/(LOOKUP(A3,'T&O Pay Scale - 350'!$A$2:$A$22,'T&O Pay Scale - 350'!$C$2:$C$22)"

I want it to look up

"O3/(LOOKUP(L3,'T&O Pay Scale - 322 - Admin'!$A$3:$A$23,'T&O Pay Scale - 322 - Admin'!$C$3:$C$23)),IF(AND(M3>0,A3>31),O3/(LOOKUP(A3,'T&O Pay Scale - 350'!$A$2:$A$22,'T&O Pay Scale - 350'!$C$2:$C$22)"

Thank you for your help

**arlu1201** · 10-04-2011, 03:33 AM

Can you please attach a sample workbook? It will be easier to test the formula.

**stait** · 10-04-2011, 07:24 AM

Hi there,

Thank you for your reply. There is an attachment with an example of what I am dealing with and looking to do. I do hope that helps.

Thank you again

**arlu1201** · 10-04-2011, 07:35 AM

So you would like to have the 1st 2 conditions along with the 3 conditions all in one cell, you mean?

**arlu1201** · 10-04-2011, 07:41 AM

I think you can just replace A3 with L3. What is the issue you are facing?

**stait** · 10-04-2011, 07:49 AM

Right now, I have it working with the first set of conditions.....what I want is it to work with all the above conditions.....and depending on which one, it would look up based on the different variables:

if N5 if greater than 0 and A5 if less than 32, ..... the Lookup shown
If N5 is greater than 0 and A5 is greater than 31, ..... the Lookup shown
....then look up using "O3/(LOOKUP(A3,'T&O Pay Scale - 322 - Admin'!$A$3:$A$23,'T&O Pay Scale - 322 - Admin'!$C$3:$C$23)),IF(AND(M3>0,A3>31),O3/(LOOKUP(A3,'T&O Pay Scale - 350'!$A$2:$A$22,'T&O Pay Scale - 350'!$C$2:$C$22)"

however, if L5 is greater than A5 and N5 if greater than 0 and A5 if less than 32,
..... then use "O3/(LOOKUP(L3,'T&O Pay Scale - 322 - Admin'!$A$3:$A$23,'T&O Pay Scale - 322 - Admin'!$C$3:$C$23)),IF(AND(M3>0,A3>31),O3/(LOOKUP(A3,'T&O Pay Scale - 350'!$A$2:$A$22,'T&O Pay Scale - 350'!$C$2:$C$22)"

Sorry....not sure how to explain this well

**arlu1201** · 10-04-2011, 07:53 AM

Maybe you could just put your conditions in points -
1. If N5 is greater than 0 and A5 is less than 32, use cell M5 (just an example)
2. If N5 is greater than 0 and A5 is greater than 31, use cell N5 (just an example)

This will make it easy to understand your question and give you the solution you need.

**stait** · 10-04-2011, 10:11 AM

Thank you for your help. I managed to figure this out. I had to change things sligtly and create a new column, but it works.

Thank you again for your help,.