 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / February 2007Vlookup 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 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)  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > HI Group, > I have been asked to work out a risk assessment based on some of the [quoted text clipped - 22 lines] > Thanks > Chris > Change the table slighlty > [quoted text clipped - 51 lines] > > - 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 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. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> Change the table slighlty >> [quoted text clipped - 62 lines] > Thanks > Chris > 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 [quoted text clipped - 80 lines] > > - 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,Expenditure,3,TRUE)+ VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,ProjectType,3,FALSE) +VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureBody,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,FinalRecipient,3,FALSE) 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,ProjectType,3,FALSE)+ VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureBody,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,FinalRecipient,3,FALSE)),"", VLOOKUP(C7,Expenditure,2,TRUE)*VLOOKUP(C7,Expenditure,3,TRUE)+ VLOOKUP(D7,ProjectType,2,FALSE)*VLOOKUP(D7,ProjectType,3,FALSE)+ VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureBody,3,TRUE)+ VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,FinalRecipient,3,FALSE)) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> 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 [quoted text clipped - 92 lines] > +VLOOKUP(E7,MeasureBody,2,TRUE)*VLOOKUP(E7,MeasureBody,3,TRUE)+ > VLOOKUP(F7,FinalRecipient,2,FALSE)*VLOOKUP(F7,FinalRecipient,3,FALSE) > The normal way to handle #N/A is as follows > [quoted text clipped - 121 lines] > > - Show quoted text - Bob, Just back to work...works perfect...thanks very much Chris. 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 > HI Group, > I have been asked to work out a risk assessment based on some of the [quoted text clipped - 22 lines] > Thanks > Chris |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.