Index Match for 2 columns and one Row

[markelbourno]

How can I match Loan amount with FICO and get the adj value for it?

A B C D
E
1Loan FICO 70% 70.01 75.01
2 $650,000.01 620 0.123 0.250 0.850
3 $650,000.01 660 (0.123) (0.250) (0.375)
4 $1,000,000.01 740 0.123 0.250 0.375
5 $1,000,000.01 680 0.123 0.250 0.375
6 $1,500,000.01 680 0.123 0.250 0.375

Please help I tried everything possible

 

[Guest]

Assuming your table is in A1:E6, G1 = Loan amount, H1 = FICO score, I1 = adj
value you want to find (70%, 70.01, or 75.01), try

=INDEX(A1:E6,MATCH(G1&H1,A1:A6&B1:B6,0),MATCH(I1,A1:E1,0))
entered with Cntrl+Shift+Enter or you'll get #VALUE!

or, if your table is a named range (referring to A1:E6 - I used TABLE), try:
=INDEX(TABLE,MATCH(G1&H1,INDEX(TABLE,0,1)&INDEX(TABLE,0,2),0),MATCH(I1,INDEX(TABLE,1,0),0))
also entered with Cntrl+Shift+Enter

 

[markelbourno]

I still get #N/A
=INDEX(Matrix,MATCH(LoanAmount&FICOScore,LoanAmountMatrix&FICOScoreMatrix,0),MATCH(LTVMatrix,Values!E149:J149,0))

What seems the problem here? *

 

[Biff]

You probably don't want a MATCH TYPE of 0 (exact match only).

Try changing it to 1. Requires that your table be sorted in ascending order
(which at least your sample is).

Biff

 

[Guest]

If the issue is not approximate match versus exact match as Biff suggested,
you'll need to post details about your named ranges, what you're trying to
look up and what results are expected.

 

[Biff]

Hmmm......

Because you're concatenating 2 numeric values:

LoanAmount&FICOScore
LoanAmountMatrix&FICOScoreMatrix

An approximate match (match type of either 1 or -1) probably won't work
either.

Biff

 

[markelbourno]

Ok let me try to do it this way
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

A B C D E F G H I
1 LTV 0 70.01 75.01 80.01 85.01 90.01
2 Loan Size FICO
3 $650,000.01 620 659 1.000 2.000 3.000 N/A N/A N/A
4 $650,000.01 660 0.000 0.000 0.000 0.000 0.000 N/A
5 $1,000,000.01 740 4.000 5.000 N/A N/A N/A N/A
6 $1,000,000.01 680 739 6.000 7.000 N/A N/A N/A N/A
7 $1,500,000.01 680 8.000 N/A N/A N/A N/A N/A

 

[Guest]

Where
A2:A6 = Matrix of Loan Amounts
B2:B6 = Matrix of FICO scores
D2:I6 = Matrix of Adj values you want returned
D1:I1 = Matrix of LTV scores

A10 = Loan amt you're looking for
A11 = FICO score you're looking for
A12 = LTV score you're looking for

Also, I'm assuming your table is sorted using loan amount as the primary key
(ascending) and FICO score as secondary key (also ascending).

Try this (array entered with Cntrl+Shift+Enter or you'll get #VALUE!):

=INDEX(D2:I6,MATCH(A11,IF(A10-A2:A6=MIN(IF(A10-A2:A6>=0,A10-A2:A6,"")),B2:B6,""),1),MATCH(A12,D1:I1,1))

It should match the largest item that is less than your criteria for Loan
amount, FICO, and LTV.

 

[markelbourno]

I did work but not for all values.

I'm trying to see if I can do it someother way as below:

=IF((I16>1500000>3000000,(INDEX(C10:H11,MATCH(I17,B10:B11,0),MATCH(I18,C5:H5,1),1)),"N/A"),IF(I16>1000000&I16>1500000,INDEX(C8:H9,MATCH(I17,B8:B9,1),MATCH(I18,C5:H5,1),1),"N/A"),IF(I16>650000&I16>1000000,INDEX(C6:H7,MATCH(I17,B6:B7,1),MATCH(I18,C5:H5,1),1)),"N/A"))

but IF isn't working the way I would like it too

 

[markelbourno]

I did work but not for all values.

I'm trying to see if I can do it someother way as below:

=IF((I16>1500000>3000000,(INDEX(C10:H11,MATCH(I17,B10:B11,0),MATCH(I18,C5:H5,1),1)),"N/A"),IF(I16>1000000&I16>1500000,INDEX(C8:H9,MATCH(I17,B8:B9,1),MATCH(I18,C5:H5,1),1),"N/A"),IF(I16>650000&I16>1000000,INDEX(C6:H7,MATCH(I17,B6:B7,1),MATCH(I18,C5:H5,1),1)),"N/A"))

but IF isn't working the way I would like it too

 

[Guest]

Which values are not working, what results did you get and what results are
expected? Are you trying to match the Loan amount exactly or approximately?

For your formula, you cannot write a conditional statement like this
I16>1500000>3000000 or this 1500000 < I16 < 3000000
If I16 is greater than 3000000, then it is certainly greater than 1500000.

generally speaking, you would use
IF(AND(condition1, condition2, condition3, etc), ...., ....)
IF(OR(condition1, condition2, condition3, etc), ...., .....)

Also, IF statements only have 3 parameter
=IF((I16>1500000>3000000,(INDEX(C10:H11,MATCH(I17,B10:B11,0),MATCH(I18,C5:H5,1),1)),"N/A")
is 3 parameters, everything after that is not valid.

You probably want something like
IF(I16>3000000, INDEX(.......), IF(I16>1500000, INDEX(.....),
IF(I16>1000000, INDEX(....), IF(I16>650000, INDEX(.......), "N/A"))))

As long as you set up the conditional tests in descending order, you don't
need to test to see if your value is between two numbers. I try to avoid
nested IF's due to excel's 7 level limit, so I use Lookups or Match whenever
possible.

 

[markelbourno]

ok that's what I have:
=IF(B16>3000000,INDEX(C12:H13,MATCH(B17,B12:B13,-1),MATCH(B18,C5:H5,1),IF(B16>1500000,INDEX(C10:H11,MATCH(B17,B10:B11,-1),MATCH(B18,C5:H5,1),IF(B16>1000000,INDEX(C8:H9,MATCH(B17,B8:B9,1),MATCH(B18,C5:H5),IF(B16>650000,INDEX(C6:H7,MATCH(B17,B6:B7,1),MATCH(B18,C5:H5,1),1))))))
based on the below grid

A B C D E F G H
1 0 70.01 75.01 80.01 85.01 90.01
2 0 0 N/A N/A N/A N/A N/A N/A
3 650000 0 619 N/A N/A N/A N/A N/A N/A
4 650000.01 620 adj1 adj2 adj3 adj4 adj5 adj6
5 650000.01 660 adj7 adj8 adj9 adj10 adj11 adj12
6 1000000.01 680 adj13 adj14 adj15 adj16 adj17 adj18
7 1000000.01 740 adj19 adj20 adj21 adj22 adj23 adj24
8 1500000.01 680 adj25 adj26 adj27 adj28 adj29 adj30
9 1500000.01 999 adj31 adj32 adj33 adj34 adj35 adj36

loaan amount=B16 66000
FICO= B17650
LTV= B18 70
result should be based on the abov example is adj1