Retrieving an Item from a List that Meets Multiple Criteria

My Account

Check out

Shopping Cart

JOIN OUR NEWSLETTER
and receive for joining:
	Free eBook Learn More!
	Free Excel ADD-IN
	Free Weekly Excel Tip








F1 eBook (Spanish)

» Retrieving an Item from a List that Meets Multiple Criteria

CATEGORY: Excel Formula and Function

VERSIONS: All Microsoft Excel Versions

Problem:

Range A2:C7 contains a list of items and their corresponding colors and prices.
We want to find the name of the first Blue item that costs less than $200.

Solution:
Use the INDEX and MATCH functions as shown in the following Array formula:
{=INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7
Example:

Item______Color____Price___Criteria
Shoes_____Red______$140____Blue
Jacket____Brown____$300____$200
Shoes_____Blue_____$199
T-Shirt___Green____$10
Jeans_____Blue_____$150
Shoes_____Black____$99

Result_____Shoes

Screenshot // Retrieving an Item from a List that Meets Multiple Criteria

Rate this tip



1	2	3	4	5

RATING: 3.19
VIEWS: 14591

Email Tip

Print Tip

Add to My Tips

READER COMMENTS (view all comments)

Issues with example
The Bobka wrote on October 07, 2005 14:51 EST

I tried setting up the example in a blank worksheet and it was not able to come back with "Shoes". Is there some formatting issues here, or is there a version problem, something missing from the formula?

tmarsh wrote on December 28, 2006 03:29 EST

it worked for me!

i copied the table from the web page table, pasted into A1, converted all "_" to spaces, deleted all excess spaces (using ASAP utilities), then used 'text to columns...' to convert the text to multiple columns (using the space character as the delimiter).

the forumla as shown does not work; you have to remove the curly brackets at either end, then press <CTRL>+<SHIFT>+<ENTER> together (which makes it an array formula and puts the curly brackets back in) ;)

Not me.
DCPHOLD wrote on December 31, 1969 19:00 EST

I followed same method as tmarsh and although it returns shoes, it only ever returns shoes! Even with no criteria the formula results in shoes. What have I missed?

Mistake in formula
Chris Barlow wrote on December 31, 1969 19:00 EST

The last part is missing an = sign because it should be less than or equal to i.e., (C2:C7 <= D3)))}

If you change the criteria it should now work

Retrieving an Item from a List that Meets Multiple Criteria
M.Hussain wrote on December 31, 1969 19:00 EST

I tried this formula {=INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7<D3)))}
but its not working may be I am missing some things please reply me regarding this error at my following. Thanks
hussain_detho@yahoo.com

Graham wrote on December 31, 1969 19:00 EST

For this to work properly, the Match_type of the MATCH function must be specified as "0". If omitted, it is assumed as "1" - finds the largest value less than or equal to the lookup value, and requires that the array values be in ascending order. The formula should be:
{=INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7<D3),0))}

Doubt
Kannan wrote on December 31, 1969 19:00 EST

{=INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7<D3),0))}

In the above formula u r using multiplication (*) symbol. When u use division (/) symbol also it returns u the same value. What is the difference?

What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade

The Complete Book of Business Plans: Simple Steps to Writing a Powerful Business Plan (Small Business Sourcebooks)

How to Use Financial Statements: A Guide to Understanding the Numbers

Personal Finance for Dummies

Special Edition Using Microsoft Outlook 2002

Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider

RELATED MICROSOFT EXCEL TIPS

• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books

VIEW ALL BOOKS

	Advertise With Us
Tips	Add-In in VBA \| Applications - Word, Outlook in VBA \| Array Formulas \| Cells, Ranges, Rows, and Columns in VBA \| Counting \| Custom Functions \| Custom Functions in VBA \| Database Formulas \| Database in VBA \| Date & Time Formulas \| Date & Time in VBA \| Events in VBA \| Excel 2003 \| Excel Chart \| Excel Consolidating \| Excel Counting \| Excel Custom Functions using VBA \| Excel Customizing \| Excel Data \| Excel Dates \| Excel Editing \| Excel Files \| Excel Filter \| Excel Format \| Excel Formula \| Excel General \| Excel Grouping and Outlining \| Excel Importing Text Files \| Excel Information \| Excel Keyboard Shortcuts \| Excel Loan Formulas \| Excel Macros - VBA \| Excel Pivot Tables \| Excel Printing \| Excel Range Name \| Excel Security - Protection \| Excel Sorting \| Excel Style \| Excel Subtotals \| Excel Summing \| Excel Text \| Excel Time \| Excel Tools \| Excel Worksheet, Workbook \| Files, Workbook, and Worksheets in VBA \| Financial Formulas \| Formating in VBA \| General Topics in VBA \| Import and Export in VBA \| Information Formulas \| Keyboard & Other Shortcuts in VBA \| Keyboard Formula Shortcuts \| Links between Worksheet and Workbooks \| Links in VBA \| Logical Formulas \| Lookup Formulas \| Mail - Send and Receive in VBA \| Menus, Toolbars, Status bar in VBA \| Modules, Class Modules in VBA \| Other Q&A Formulas \| Printing in VBA \| Protecting in VBA \| Summing \| Text Formulas \| User Forms, Input boxes in VBA \| Using Loops \| Working with Formulas \|
Tips by Version	Microsoft Excel 97 \| Microsoft Excel 2000 \| Microsoft Excel 2002 \| All Microsoft Excel Versions \| New in Excel 2002 \| New in Excel 2003 - Office 11
Website	Home \| Tip of Hour \| Recommended Tips \| Most Viewed Tips \| Tips by Version \| Submit a Tip \| My Tips Microsoft Excel Tutorials \| Excel Links \| Write for Us \| About Us \| Search Results \| Tip Archives \| Excel Forum \| Excel Forum Archives
Excel Book	Excel 97 Book \| Excel 2000 Book \| Excel 2002 Book \| Excel XP Book \| Book Store

Terms and Conditions of use The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Copyright © 2003 ExcelTip.com Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation Site Developed By: Varien