index match

[Guest]

I am looking for the value when Loan amount is $670,000 with 640 FICO
and LTV 76 and that should give me 3.000
LTV 0 70.01 75.01 80.01 85.01 90.01
Loan Size FICO
$650,000.01 620 659 1.000 2.000 3.000 N/A N/A N/A
$650,000.01 660 0.000 0.000 0.000 0.000 0.000 N/A
$1,000,000.01 740 4.000 5.000 N/A N/A N/A N/A
$1,000,000.01 680 739 6.000 7.000 N/A N/A N/A N/A
$1,500,000.01 680 8.000 N/A N/A N/A N/A N/A
that's what I have and it can't seem to work
=INDEX(D3:I7,MATCH(B11,D1:I1,1),MATCH(B10&B9,A3:A7&B3:B7,1),1)
I tried too but nothing
=INDEX(D3:I7,MATCH(B11,D1:I1,0),MATCH(B10&B9,A3:A7&B3:B7,0))

 

[Bob Phillips]

=index(f3:f7,match(1,(a3:a7=670000)*(b3:b67=640),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[markelbourno]

the loan anount $670,000 with 640 FICO and LTV 76 change base on the
user entery
Is there away for it?

 

[Bob Phillips]

Don't understand that statement.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[markelbourno]

I am trying to get the value of adj based on Loan Amount, Fico Score
and LTV%the value for loan amount, FICO score and LTV change

 

[vezerid]

Bob,
essentially he wants to perform inexact match. Column A:A contains loan
amts. Col B:B contains the FICO (whatever it is). Row 1 contains the
LTV (whatever it is).

From what I can gather from the example, the OP wants to match loan amt

670,000 with 650,000; FICO of 640 with 650; and LTV of 76 with 75.01.

Seems very tricky and I don't know what he wants if, e.g., we have
principal 780,000, FICO of 800 and LTV of 88. At this moment I don't
know myself how to attack this and if it can be attacked with
formula-only solution.

Regards,
Kostis

 

[Roger Govier]

Hi

I'm not sure that I understand what you want, but maybe
=INDEX(D2:I7,MIN(MATCH(B9,A3:A7,1),MATCH(B10,B3:B7,1))+1,MATCH(B11,D2:I2,1))