Advanced Lookup Formula to Show 3 Lowest Prices with Supplier Name, Including Ties. EMT 1737.

Posted on by Bb

Download Excel File: https://excelisfun.net/files/EMT1737.xlsx
Learn how to lookup and display the three lowest transportation prices and transporter name from a large lookup table with From Location, To Location and Size of Truck. There-way lookup to return multiple records. See the functions: XLOOKUP, FILTER, XMATCH, LET, SORT, COUNTIFS, SMALL and ISNUMBER.
Learn how to:
1. (00:00) Explanation of the there-way lookup to return multiple records
2. (00:35) Intro Song
3. (00:42) Older Methods if you do NOT have Microsoft 365
4. (01:08) Lookup price column with XLOOKUP
5. (01:51) FILTER to filter prices to get the third smallest value.
6. (02:26) Look at semi-colons in array that instruct FILTER Dynamic Array Function to filter by rows.
7. (02:46) SMALL function to get third smallest price.
8. (03:09) FILTER function to filter by rows.
9. (03:40) Discussion of AND Logical tests in include argument of FILTER, including why the formula returns 0 and 1 values.
10. (04:45) Vertical Array that allows FILTER to filter by rows.
11. (05:29) FILTER function to filter columns. Use XMATH and ISNUMBER functions.
12. (06:13) Look at columns in resultant array that instruct FILTER Dynamic Array Function to filter by columns.
13. (07:08) Bonus #1. COUNTIFS and IF function to deal with situations where there are fewer records than three. Use these in k argument of SMALL.
14. 07:53) Bonus #2: LET function to define variable when there are duplicate formula elements. Leads to a more efficient calculating formula. Easier to read too.
15. (08:40) SORT Dynamic Array Function to sort the transportation prices from smallest to biggest.
16. (09:10) Summary of formulas and functions used in video.
17. (09:25) Summary, Closing and Video Links

How to accomplish if you do not have Microsoft 365. Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates, https://www.youtube.com/watch?v=rKDI-kdBsjY

Lookup 3 smallest values. Retrieve three smallest values. One Lookup value, return multiple records. Three Lookup Values. Three Way Lookup. Extract top values, Lookup and display lowest bids. How to lookup and return multiple values. To 10 values. Top three values. Top five values. Transportation Lookup Table. Complex Lookup. Three lookup values to get lowest supplier bids.

ExcelIsFunWed, August 4, 2021 8:08pm