HLOOKUP To Return Multiple Items In Excel

Of all lookup functions, HLOOKUP is among the least famous ones.

It looks up a value in the first row and returns any corresponding item in another row.

Look up value MUST be in the top row of a table.

How do you make it return multiple items?

It’s default setting is to return a single value

Using the data below, how do you return the least expensive suppliers per item quoted?

Notes:

1. To return multiple items you need to use the column value as a lookup value
2. You need to create a table array where the Column Values are on the top row
3. You need to loop through column values one at a time

=IFERROR(
   HLOOKUP(
     SMALL(
        IF(tblQuote[@]=MIN(tblQuote[@]),COLUMN(tblQuote[@])),
        COLUMN(A1)),
     CHOOSE(
        {1;2},COLUMN(tblQuote[@]),tblQuote[#Headers]),
     2,FALSE
    ),
"")

DOWNLOAD WORKSHEET

Watch the Full Video on LOOKUP functions Returning Multiple Items