I need to find a formula that will give me the value of a cell in another table. And there are four criteria that needs to be met. so the user will enter the age, gender, tobacco use and a product type. Once those four choices are made the formula should give us a dollar amount from a cell and another table.
Working in insurance we need to be able to calculate insurance quotes quickly. I’ve created a calculator to do that but a lot of the information has to be done manually leaving room for user error. With the calculator this formula will do it automatically without error since they are only making selections. Our company has it setup on the website however it takes longer requiring all their details. I need to be able to show them accurate quote quickly before we get get to the actual application.
—–
Quincy
LOOKUP is the function you want. But the trick is to do the LOOKUP on multiple columns. You do this by creating an extra column that combines those cells.
So if A, B, C and D are age, gender, tobacco use and a product type, then E should be a formula that combines them. Like =A2&"-"&B2&"-"&C2&"-"&D2. A value there may look like "40-male-true-A." Then column F would be the dollar amount to return as a result.
So in your first table, you need to do the same combination to get the cell with the four values combined with dashes in between. Then use that value in the LOOKUP to get the dollar amount from the second table.