Question

can you please give me give the correct formula for Vlookup function where the interest rate is based on the credit rating and the external data table. i have tried it in cell B15 but cant figure out why the external data cells F3:H:6 wont work

EXTERNAL DATA CUSTOMER INFORMATION Interest Rate 0.08 0.06 0.05 0.04 Credit Rating Value rating William Couch СМЕ Employer (Ticker Symbo) CME FICO Credit Rating Application Date 500 600 700 800 3/1/2019 3/14/2019 11:01 6 Today's Date LOAN INFORMATION 9 Purchase Price 0 Down Payment 11 Assessment 300,000 3000 Q1 If the credit rating is increased from 620 Q2 part 1 At 710 credit rating, how does an addition 02 part 2 When is the debt paid off now compared 297,000 13 Loan Amount 14 Loan Term years nterest Rate 16 Paymernt 17 Additional Principal UP(lookup_value, table array, col index.num, (range lookup) per month Interest Paid Additional Running Total Running Ending Banalce Beginning Balance Principal Paid Principal Interest Principal

Answer 1

Use the following formula in Cell B15...

=VLOOKUP(B4,F3:H6,3,TRUE)

Where B4 - Key

F3:H6 - Data Range

3 - Col_index of Interest rate

TRUE - optional for sorting .It indicates whether the column to be searched (the first column of the specified range) is sorted.

In your formula you can change 'H' to 3... Then it should work perfectly.

Screenshot of output :-

(You can view at formula bar)

-VLOOKUP (B4, F3:H6, 3, TRUE) Credit Rating Value rating Interest Rate 0.08 0.06 0.05 0.04 4 FICO Credit Rating 620 600 700 800 4 10 12 13 14 15 Interest Rate 0.06