200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi,
How can I resolve this? I have used if statement but I could not figure it out.
I have this discount table and also I have two cells A1 and A2. In A1 I input the color and B1 I input the discount according to the table, and C1 will have the formula. If I input (A1)yellow (B1) 8, it should say "Pass". If I input (A1)yellow (B1) 11, it should say "Fail"
Yellow and >10
Purple and >15
Orange and >20
Thanks,
Vivi
This formula will do the job.
=IF($B1 > INDEX(Discounts,MATCH($A1,Colors,0)),"Passed","Failed")It uses the named ranges Colors and Discounts which are explained in the attached workbook where you will find the formula in F1. Of cours, it could be anywhere, including C1, but in the attached workbook I used C1:E1 to show the stages of formula development, including the formula you probably tried, in E1. Change A1 and B1 to see the varying results.
Note that it's the better practice to use named ranges in formulas rather than real addresses because they are easier to manage as well as read. If a range ever changes, or you even want it to be dynamic, the place to change or program should be just one - in the Name Manager - and not a potentially huge number of formulas all over the workbook.