How to Use the SMALL function to compare prices in Excel
In the example, a worksheet lists offers from different suppliers. To make a decision as to which is the best offer, the SMALL function can be used to check for the lowest price. As in our previous tip, you can use the INDEX and MATCH functions to get the names of the companies.
To find out the three cheapest offers and their supplier:
- In cells B2:B10 enter the offers.
- Number the cells C2:C4 with 1, 2, and 3.
- Select cells D2:D4 and type the following formula: =INDEX($A$2:$A$10,MATCH(SMALL($B$2:$B$10,C2),$B$2:$B$10,0)).
- Press Ctrl+Enter.
- Select cells E2:E4 and type the following formula: =SMALL($B$2:$B$10,C2).
- Press Ctrl+Enter.
This tip is compatible with Excel 97, 2000, 2003 and 2007.
- How to Use the MROUND function to round prices to 5 or 25 cents
- Using the EXACT function to compare two columns in Excel
- Use the INDEX, MATCH, and LARGE functions to determine the best salesperson
- How to use the PV function to compare investments in Excel
- Identify missing numbers in a consecutive list using Excel
