|
Vlookup question
HI Group,
I have been asked to work out a risk assessment based on some of the following grid/variables (there are 3 more to build...) Loan Rating Weight Item Rating Weight 10000 1 4 Capital 1 3 25000 2 4 Revenue 2 3 50000 3 4 Unknown 3 3 75000 4 4 100000 5 4 1000000 6 4 For example, If an applicant has the following criteria... Loan Item 15000 Revenue ....then the risk rating is (2 x 4) + (2 x 3) = 14 I have done a bit of searching and think I need Vlookup but I am not sure to implement the logic. Can someone help. Thanks Chris |
Vlookup question
Change the table slighlty
Loan Rating Weight Item Rating Weight 0 1 4 Capital 1 3 10000 2 4 Revenue 2 3 25000 3 4 Unknown 3 3 50000 4 4 75000 5 4 100000 6 4 and use =VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+ VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... HI Group, I have been asked to work out a risk assessment based on some of the following grid/variables (there are 3 more to build...) Loan Rating Weight Item Rating Weight 10000 1 4 Capital 1 3 25000 2 4 Revenue 2 3 50000 3 4 Unknown 3 3 75000 4 4 100000 5 4 1000000 6 4 For example, If an applicant has the following criteria... Loan Item 15000 Revenue ...then the risk rating is (2 x 4) + (2 x 3) = 14 I have done a bit of searching and think I need Vlookup but I am not sure to implement the logic. Can someone help. Thanks Chris |
Vlookup question
On Feb 15, 9:33 am, "Bob Phillips" wrote:
Change the table slighlty Loan Rating Weight Item Rating Weight 0 1 4 Capital 1 3 10000 2 4 Revenue 2 3 25000 3 4 Unknown 3 3 50000 4 4 75000 5 4 100000 6 4 and use =VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+ VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... HI Group, I have been asked to work out a risk assessment based on some of the following grid/variables (there are 3 more to build...) Loan Rating Weight Item Rating Weight 10000 1 4 Capital 1 3 25000 2 4 Revenue 2 3 50000 3 4 Unknown 3 3 75000 4 4 100000 5 4 1000000 6 4 For example, If an applicant has the following criteria... Loan Item 15000 Revenue ...then the risk rating is (2 x 4) + (2 x 3) = 14 I have done a bit of searching and think I need Vlookup but I am not sure to implement the logic. Can someone help. Thanks Chris- Hide quoted text - - Show quoted text - HI Bob, Thanks for the reply and suggestion. I was hoping maybe for a more dynamic solution as I have to apply this logic to 1500 applications which span 5 sets of text variables. If you could supply the basics I was hoping to extend it to 5 sets of variables. Thanks Chris |
Vlookup question
How about insert 1 column in both sheets combine these 2 together say:
Index Loan Rating Weight Item Rating Weight 10000Capital 10000 1 4 Capital 1 3 25000Revenue 25000 2 4 Revenue 2 3 and vlookup the index " wrote: HI Group, I have been asked to work out a risk assessment based on some of the following grid/variables (there are 3 more to build...) Loan Rating Weight Item Rating Weight 10000 1 4 Capital 1 3 25000 2 4 Revenue 2 3 50000 3 4 Unknown 3 3 75000 4 4 100000 5 4 1000000 6 4 For example, If an applicant has the following criteria... Loan Item 15000 Revenue ....then the risk rating is (2 x 4) + (2 x 3) = 14 I have done a bit of searching and think I need Vlookup but I am not sure to implement the logic. Can someone help. Thanks Chris |
Vlookup question
Could you expand, that looks pretty dynamic to me. You don't have to
hard-code the test values, you could put those in cells and reference the cells. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... On Feb 15, 9:33 am, "Bob Phillips" wrote: Change the table slighlty Loan Rating Weight Item Rating Weight 0 1 4 Capital 1 3 10000 2 4 Revenue 2 3 25000 3 4 Unknown 3 3 50000 4 4 75000 5 4 100000 6 4 and use =VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+ VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... HI Group, I have been asked to work out a risk assessment based on some of the following grid/variables (there are 3 more to build...) Loan Rating Weight Item Rating Weight 10000 1 4 Capital 1 3 25000 2 4 Revenue 2 3 50000 3 4 Unknown 3 3 75000 4 4 100000 5 4 1000000 6 4 For example, If an applicant has the following criteria... Loan Item 15000 Revenue ...then the risk rating is (2 x 4) + (2 x 3) = 14 I have done a bit of searching and think I need Vlookup but I am not sure to implement the logic. Can someone help. Thanks Chris- Hide quoted text - - Show quoted text - HI Bob, Thanks for the reply and suggestion. I was hoping maybe for a more dynamic solution as I have to apply this logic to 1500 applications which span 5 sets of text variables. If you could supply the basics I was hoping to extend it to 5 sets of variables. Thanks Chris |
Vlookup question
On Feb 15, 11:12 am, "Bob Phillips" wrote:
Could you expand, that looks pretty dynamic to me. You don't have to hard-code the test values, you could put those in cells and reference the cells. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... On Feb 15, 9:33 am, "Bob Phillips" wrote: Change the table slighlty Loan Rating Weight Item Rating Weight 0 1 4 Capital 1 3 10000 2 4 Revenue 2 3 25000 3 4 Unknown 3 3 50000 4 4 75000 5 4 100000 6 4 and use =VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+ VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message groups.com... HI Group, I have been asked to work out a risk assessment based on some of the following grid/variables (there are 3 more to build...) Loan Rating Weight Item Rating Weight 10000 1 4 Capital 1 3 25000 2 4 Revenue 2 3 50000 3 4 Unknown 3 3 75000 4 4 100000 5 4 1000000 6 4 For example, If an applicant has the following criteria... Loan Item 15000 Revenue ...then the risk rating is (2 x 4) + (2 x 3) = 14 I have done a bit of searching and think I need Vlookup but I am not sure to implement the logic. Can someone help. Thanks Chris- Hide quoted text - - Show quoted text - HI Bob, Thanks for the reply and suggestion. I was hoping maybe for a more dynamic solution as I have to apply this logic to 1500 applications which span 5 sets of text variables. If you could supply the basics I was hoping to extend it to 5 sets of variables. Thanks Chris- Hide quoted text - - Show quoted text - Thank you Bob, never thought I could change your coded values...doh!!! One more thing, some cells have blanks so my formulas return #N/A., how can I avoid this scenario now? Here is how my code looks so far (with range names) =VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendi ture,3,TRUE)+ VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE) +VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,Measure Body,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE) |
Vlookup question
The normal way to handle #N/A is as follows
=IF(ISNA(vlookup_formula),"",vlookup_formula) You can do all 8 at once =IF(ISNA(VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7 ,Expenditure,3,TRUE)+ VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)+ VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureB ody,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)),"", VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendit ure,3,TRUE)+ VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)+ VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureB ody,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... On Feb 15, 11:12 am, "Bob Phillips" wrote: Could you expand, that looks pretty dynamic to me. You don't have to hard-code the test values, you could put those in cells and reference the cells. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... On Feb 15, 9:33 am, "Bob Phillips" wrote: Change the table slighlty Loan Rating Weight Item Rating Weight 0 1 4 Capital 1 3 10000 2 4 Revenue 2 3 25000 3 4 Unknown 3 3 50000 4 4 75000 5 4 100000 6 4 and use =VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+ VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message groups.com... HI Group, I have been asked to work out a risk assessment based on some of the following grid/variables (there are 3 more to build...) Loan Rating Weight Item Rating Weight 10000 1 4 Capital 1 3 25000 2 4 Revenue 2 3 50000 3 4 Unknown 3 3 75000 4 4 100000 5 4 1000000 6 4 For example, If an applicant has the following criteria... Loan Item 15000 Revenue ...then the risk rating is (2 x 4) + (2 x 3) = 14 I have done a bit of searching and think I need Vlookup but I am not sure to implement the logic. Can someone help. Thanks Chris- Hide quoted text - - Show quoted text - HI Bob, Thanks for the reply and suggestion. I was hoping maybe for a more dynamic solution as I have to apply this logic to 1500 applications which span 5 sets of text variables. If you could supply the basics I was hoping to extend it to 5 sets of variables. Thanks Chris- Hide quoted text - - Show quoted text - Thank you Bob, never thought I could change your coded values...doh!!! One more thing, some cells have blanks so my formulas return #N/A., how can I avoid this scenario now? Here is how my code looks so far (with range names) =VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendi ture,3,TRUE)+ VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE) +VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,Measure Body,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE) |
Vlookup question
On Feb 15, 4:34 pm, "Bob Phillips" wrote:
The normal way to handle #N/A is as follows =IF(ISNA(vlookup_formula),"",vlookup_formula) You can do all 8 at once =IF(ISNA(VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7 ,Expenditure,3,TRUE)+ VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)+ VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureB ody,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)),"", VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendit ure,3,TRUE)+ VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE)+ VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureB ody,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... On Feb 15, 11:12 am, "Bob Phillips" wrote: Could you expand, that looks pretty dynamic to me. You don't have to hard-code the test values, you could put those in cells and reference the cells. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message roups.com... On Feb 15, 9:33 am, "Bob Phillips" wrote: Change the table slighlty Loan Rating Weight Item Rating Weight 0 1 4 Capital 1 3 10000 2 4 Revenue 2 3 25000 3 4 Unknown 3 3 50000 4 4 75000 5 4 100000 6 4 and use =VLOOKUP(15000,A2:C7,2,TRUE)*VLOOKUP(15000,A2:C7,3 ,TRUE)+ VLOOKUP("Revenue",D2:F4,2,FALSE)*VLOOKUP("Revenue" ,D2:F4,3,FALSE) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message groups.com... HI Group, I have been asked to work out a risk assessment based on some of the following grid/variables (there are 3 more to build...) Loan Rating Weight Item Rating Weight 10000 1 4 Capital 1 3 25000 2 4 Revenue 2 3 50000 3 4 Unknown 3 3 75000 4 4 100000 5 4 1000000 6 4 For example, If an applicant has the following criteria... Loan Item 15000 Revenue ...then the risk rating is (2 x 4) + (2 x 3) = 14 I have done a bit of searching and think I need Vlookup but I am not sure to implement the logic. Can someone help. Thanks Chris- Hide quoted text - - Show quoted text - HI Bob, Thanks for the reply and suggestion. I was hoping maybe for a more dynamic solution as I have to apply this logic to 1500 applications which span 5 sets of text variables. If you could supply the basics I was hoping to extend it to 5 sets of variables. Thanks Chris- Hide quoted text - - Show quoted text - Thank you Bob, never thought I could change your coded values...doh!!! One more thing, some cells have blanks so my formulas return #N/A., how can I avoid this scenario now? Here is how my code looks so far (with range names) =VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expendi ture,3,TRUE)+ VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,Project Type,3,FALSE) +VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,Measure Body,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,Fina lRecipient,3,FALSE)- Hide quoted text - - Show quoted text - Bob, Just back to work...works perfect...thanks very much Chris. |
| All times are GMT +1. The time now is 02:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com