I try to define range of year of experience by using the formula below in the attached excel. However it does not return the value that I want.
=IF(AND(A2>=0.00, A2<3.00),"'0-3",IF(AND(A2>=3.00, A2<5.00),"'3-5",IF(AND(A2>=5.00,A2<7.00),"'5-7",IF(AND(A2>=7.00,A2<10.00),"'7-10",IF(AND(A2>=10.00, A2<12.00),"'10-12",IF(AND(A2>=12.00, A2<15.00),"'12-15",IF(AND(A2>=15.00,A2<20.00),"'15-20", IF(AND(A2>=20.00, A2<25.00),"'20-25", IF(AND(A2>=25,A2<30.00),"'25-30",IF(A2>=30.00),"'>30"))
What is the reason and how to fix the formula.
Thank you.
Hi Ahmad,
IF stops evaluating when it finds a True result so you can do away with the AND functions and simplify it to:
=IF(A2<3,"'0-3", IF(A2<5,"'3-5", IF(A2<7,"'5-7", IF(A2<10,"'7-10", IF( A2<12,"'10-12", IF(A2<15,"'12-15", IF(A2<20,"'15-20", IF(A2<25,"'20-25", IF(A2<30,"'25-30", "'>30")))))))))
But a neater solution is to use a lookup like VLOOKUP
=VLOOKUP(A16,$D$3:$E$12,2)
I've included both formulae in the attached workbook.
regards
Phil
Thank you Philip for the advice on the formula and its alternative.
Question: For VLOOKUP formula, in what scenario that we can leave the range_lookup blank.
Regards
Ahmad Khalil
Hi Ahmad,
range_lookup defaults to True which means VLOOKUP will look for an approximate match. This is what you want in this scenario because you are looking up values that fall into ranges.
If you lookup 1.83, VLOOKUP matches the nearest smallest number which is 0. Obviously if you set it to exact match =VLOOKUP( A16,$D$3:$E$12,2, False) then 1.83 will not be found in your case and you'll get an error.
So, leave out range_lookup when you want an approximate match to the value you are looking up. Note that you need to have the data being looked up (Col D in this case) sorted into ascending order for this to work properly.
Please read this for more information
https://www.myonlinetraininghub.com/excel-vlookup-formulas-explained
Regards
Phil
