Solved Using IF AND with Vlookup

January 26, 2015 at 19:38:54
Specs: Windows 7
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 factors

1. 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, false

Essentially 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..


See More: Using IF AND with Vlookup

Report •

#1
January 27, 2015 at 08:17:08
✔ Best Answer
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
January 27, 2015 at 12:39:36
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!


Report •
Related Solutions


Ask Question