Forum Discussion
If a cell contains a list value then assign formula X, Y, Z?
Hi MyCrymbals82
you could create a reference table with the percentages per product. That table could be anywhere in your workbook. In my example, I just put it beside of your entry table, but that does not matter:
=I2*VLOOKUP(F2;$O$2:$P$6;2;FALSE)
So, depending on the product entered in cell F2, it looks for the matching percentage in the reference table and multiplies the premium with it.
Thanks for your reply. I'm closer now but for some reason it doesn't like the Policy Type, cell E3 (Product in your example). No idea why because there is nothing on that field.
=F3*VLOOKUP(E3;$L$2:$P$6;2;FALSE)
Hi MyCrymbals82
what happens if you enter a policy type in E3. Do you still get an error?
If E3 is empty, there will be an error because the formula tries to find an empty value in the lookup table, which does not exist there.
To avoid this, you could also enhance the formula like this:
=F3*IFERROR(VLOOKUP(E3;$L$2:$P$6;2;FALSE);0)
Thanks again for the reply. I posted this to an Excel Facebook group and the issue was the semi-colons. If I change them to commas, the formula works. I was even able to add a validation list to the Policy Type column so users can pick from a drop down.
=F3*VLOOKUP(E3,$L$2:$M$7,2,FALSE)
Only issue is the Total (pointed to in arrow #1) doesn't do the sum of the column unless every row has a currency value in Agency Comm. That formula is a simple =SUM(H3:H20). I assume the #N/A is the reason.
