| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match for 2 columns and one Row
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 :( |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match for 2 columns and one Row
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,A 1: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(TA BLE,0,2),0),MATCH(I1,INDEX(TABLE,1,0),0)) also entered with Cntrl+Shift+Enter " wrote: 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 :( |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match for 2 columns and one Row
I still get #N/A
=INDEX(Matrix,MATCH(LoanAmount&FICOScore,LoanAmoun tMatrix&FICOScoreMatrix,0),MATCH(LTVMatrix,Values! E149:J149,0)) What seems the problem here? :(* JMB wrote: 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,A 1: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(TA BLE,0,2),0),MATCH(I1,INDEX(TABLE,1,0),0)) also entered with Cntrl+Shift+Enter " wrote: 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 :( |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match for 2 columns and one Row
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 wrote in message oups.com... I still get #N/A =INDEX(Matrix,MATCH(LoanAmount&FICOScore,LoanAmoun tMatrix&FICOScoreMatrix,0),MATCH(LTVMatrix,Values! E149:J149,0)) What seems the problem here? :(* JMB wrote: 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,A 1: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(TA BLE,0,2),0),MATCH(I1,INDEX(TABLE,1,0),0)) also entered with Cntrl+Shift+Enter " wrote: 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 :( |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match for 2 columns and one Row
Hmmm......
=INDEX(Matrix,MATCH(LoanAmount&FICOScore,LoanAmoun tMatrix&FICOScoreMatrix,0),MATCH(LTVMatrix,Values! E149:J149,0)) 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 "Biff" wrote in message ... 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 wrote in message oups.com... I still get #N/A =INDEX(Matrix,MATCH(LoanAmount&FICOScore,LoanAmoun tMatrix&FICOScoreMatrix,0),MATCH(LTVMatrix,Values! E149:J149,0)) What seems the problem here? :(* JMB wrote: 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,A 1: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(TA BLE,0,2),0),MATCH(I1,INDEX(TABLE,1,0),0)) also entered with Cntrl+Shift+Enter " wrote: 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 :( |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match for 2 columns and one Row
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 Biff wrote: Hmmm...... =INDEX(Matrix,MATCH(LoanAmount&FICOScore,LoanAmoun tMatrix&FICOScoreMatrix,0),MATCH(LTVMatrix,Values! E149:J149,0)) 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 "Biff" wrote in message ... 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 wrote in message oups.com... I still get #N/A =INDEX(Matrix,MATCH(LoanAmount&FICOScore,LoanAmoun tMatrix&FICOScoreMatrix,0),MATCH(LTVMatrix,Values! E149:J149,0)) What seems the problem here? :(* JMB wrote: 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,A 1: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(TA BLE,0,2),0),MATCH(I1,INDEX(TABLE,1,0),0)) also entered with Cntrl+Shift+Enter " wrote: 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 :( |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index Match for 2 columns and one Row
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. " wrote: I still get #N/A =INDEX(Matrix,MATCH(LoanAmount&FICOScore,LoanAmoun tMatrix&FICOScoreMatrix,0),MATCH(LTVMatrix,Values! E149:J149,0)) What seems the problem here? :(* JMB wrote: 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,A 1: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(TA BLE,0,2),0),MATCH(I1,INDEX(TABLE,1,0),0)) also entered with Cntrl+Shift+Enter " wrote: 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 :( |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
