Forum Discussion
If a cell contains a list value then assign formula X, Y, Z?
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.
Hi MyCrymbals82
that's true, the regular SUM function cannot handle #N/A errors. You could use SUMIF instead:
=SUMIF(E2:E15,"<>#N/A")
Alternatively you could use the AGGREGATE function:
=AGGREGATE(9,6,E2:E15)
This one ignores all types of error.
Just change the range E2:E15 to the relevant range in your worksheet, that should fix the problem.