Lookup Value from Table B, based on result in Table A

[Doug_Ardy]

Here is the scenario. I have a formula field in Table A {Loan Amount}, that calculates the loan amount based on two other fields in Table A. Based on the resulting value in that {Loan Amount} formula field, I would like to to reference the cost of title insurance (the cost of title insurance is based on the {Loan Amount} in Table A).

Is it possible to have a field in Table A that references this cost from Table B, based on the calculated {Loan Amount} field from Table A?

If not, is there another way I could maybe handle this?

[augmented]

Hi Doug. Maybe a little more info might get some who’ve handled this problem before to jump in.

What’s the primary key in table B? Is the cost of TI based on the loan amount being in certain ranges? Or is it a more complicated calculation?

Thanks.

[Doug_Ardy]

Thank you for the reply. I wasn’t sure if there was an easy way to do this with a lookup or rollup field.

The title insurance is based on a cost per thousand. That would be an easy calculation, but the cost per thousand changes as the loan amount goes up. It is bracketed as follows:

Loan Amount:
1001 - 50,000 = $3.15 per thousand
50,001 - 100,000 = $2.50 per thousand
100,0001 - 200,000 = $2.20 per thousand
and so on.

There is also a minimum starting amount that gets added to the per thousand calculation. It complicates things further as the cost of say a $150,000 loan is determined by taking the first 50,000 at 3.15/thousand, and the next 50k at 2.50/thousand, and then the final 50k at 2.20/thousand.

My first thought was just to add a table with everything loan amount and put the associated fee next to that loan amount. If there was a way to cross-reference the Loan Amount field in Table A, with the same Loan Amount figure and Table B (and have it pull over the associated fee), I thought that would be an easy enough solution. But I can’t figure that out. I will probably just use a formula instead. I’m no math wizard so it takes me some extra time to figure it out.