digestVidSign in
summary

excelisfun - Excel Magic Trick 1311: Return Multiple Items from One Lookup Value & Show Total for Invoice Items

Thumbnail of the video “excelisfun - Excel Magic Trick 1311: Return Multiple Items from One Lookup Value & Show Total for Invoice Items”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Technology Science
Tags: technology, science, div
Views: 2 · Last viewed: 2026-10-16 00:53 UTC

Short Summary

Main recommendation: To return multiple items from one lookup value and show a total, combine INDEX with AGGREGATE (using SMALL) to pull every matching row position, then use a nested IF to append a SUMIFS total row. The result updates automatically when you change the item number in the selection cell. Standard lookup functions like VLOOKUP or XLOOKUP return only the first match, which is why this approach is needed for duplicate keys.

Key Points

  • The first step is COUNTIFS on the item number column to count how many rows match the selected item (four in the example), which sets how many results to return.
  • Standard lookup functions return only one match when duplicates exist, so INDEX must be driven by calculated relative row positions instead.
  • Relative positions are built with ROW(range) minus the header row, then divided by a TRUE/FALSE array (match or no match); dividing by zero filters out non-matching rows.
  • AGGREGATE with function 15 (SMALL) and option 6 (ignore errors) pulls the first, second, third matching positions in order as the formula is copied down.
  • A nested IF adds the total row: items show while the counter is within the match count, the row after shows SUMIFS of sales, and any remaining rows show blank text.

Details Worth Noting

  • Concrete instructions:
    • Enter the item number to look up in the selection cell.
    • In a count cell, use COUNTIFS on the item number column with the selected item to get the number of matches (the speaker's example yields 4).
    • Build the relative-position array: ROW(all item rows) minus 1 (using the header row), and lock the references with F4 so the formula copies correctly.
    • Divide that array by an array that tests whether each item number equals the selected item, so matching rows return positions and non-matches return #DIV/0!.
    • Wrap the whole thing in AGGREGATE(15, 6, positions/matches, ROWS(F$8:F8)), where 15 is SMALL, 6 ignores errors, and the ROWS term supplies 1, 2, 3, 4 as the formula copies down.
    • Put the INDEX formula in the item column, using the item names as the array and the AGGREGATE result as the row number.
    • Wrap it in a nested IF: if the counter is less than or equal to the count, return the INDEX lookup; if the counter equals count plus one, return SUMIFS(sales, item numbers, selected item); otherwise return an empty string "".
    • The formula works without Ctrl+Shift+Enter because AGGREGATE handles array arguments natively; press Ctrl+Enter, then copy down.
  • The speaker's example result: the item number displays the four associated items followed by the total.
  • Locking references with F4 is required so the formulas copy correctly down the column.
  • The speaker credits Bill Jelen for the technique of subtracting the header row cell to generate relative positions.

Bottom Line

This is a reusable, dynamic pattern: once built, changing the selected item number immediately refreshes both the list of matching items and their total, without manual filtering.