 |
 | 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 / New Users / February 2008Multiple Min & Max range lookup.
I have a list of Mins, and a list of Max's. If someone was to enter in a number in D3, I would like for it to lookup the entire list of Mins and Maxs to see if the number entered falls within range of a Min-Max row. Example, since 1801 is between 1800 and 2200, it returns the equivalent word or number in row C. If I were to put 550 in D3, it would return APPLES. If I were to put 1499 in D3, it would return CHERRIES. A B C D E MIN MAX 500 1000 APPLES 1200 1500 CHERRIES 1801 PLUMS 1800 2200 PLUMS Note: I have a long list of mins and maxs. Thanx Try this *array* formula: =INDEX(C2:C100,MATCH(1,(D3>=A2:A100)*(D3<=B2:B100),0))  SignatureArray formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== I have a list of Mins, and a list of Max's. If someone was to enter in a number in D3, I would like for it to lookup the entire list of Mins and Maxs to see if the number entered falls within range of a Min-Max row. Example, since 1801 is between 1800 and 2200, it returns the equivalent word or number in row C. If I were to put 550 in D3, it would return APPLES. If I were to put 1499 in D3, it would return CHERRIES. A B C D E MIN MAX 500 1000 APPLES 1200 1500 CHERRIES 1801 PLUMS 1800 2200 PLUMS Note: I have a long list of mins and maxs. Thanx Worked PUUUUUR-FECTLY!!! Thanx a million! Better yet, when I earn my first million, you got a Krystal on me (that's with cheese!). You're welcome, and thanks for the feed-back. BUT ... what's a Krystal? SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- Worked PUUUUUR-FECTLY!!! Thanx a million! Better yet, when I earn my first million, you got a Krystal on me (that's with cheese!). |
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.