Hi there, i am trying to find a way to come up with a formula to organise a sheet i am working on.

Basically I need to do a few factors1. If O=TRUE and R=HR then lookup column P, Foveauxrates (this is a named range),6,false

2. OR If O=TRUE and R=DY then lookup column P, Foveauxrates (this is a named range),7,false

3. OR If O=TRUE and R=WK then lookup column P, Foveauxrates (this is a named range),8,false

4. Otherwise lookup column P, Generalrates (named range), 6, falseEssentially if a statement in O it is telling me if the specific job number entered is a seperate contract to our general rates. E.g. True = Foveaux, False = General. I am needing to show up the specific rate to charge.

Also, in Column R, it depends on the asset as to whether we charge HR, DY or WK (this formula has already been set up)

I hope this makes sense..

Without specific Column Letters and Row Numbers

the best I can offers is a general format that should work.Your formula would look something like:

=IF(O1=Fales,"End",IF(R1="HR",Vlookup(P1,Foveauxrates,6,false),IF(R1="DY",Vlookup(P1,Foveauxrates,7,false),IF(R1="WK",Vlookup(P1,Foveauxrates,8,false),VLookup(P1,Generalrates,6,false)))))

MIKE

message edited by mmcconaghy

Perfect, that worked!! The formula worked out like this...

=IF(O2="FALSE","END",IF(R2="HR",VLOOKUP(P2,Foveauxrates,6,FALSE),IF(R2="DY",VLOOKUP(P2,Foveauxrates,7,FALSE),IF(R2="WK",VLOOKUP(P2,Foveauxrates,8,FALSE),VLOOKUP(P2,Generalrates,6,FALSE)))))

Amazing, thank you!

Ask Your Question

Weekly Poll