The link in the accepted answer has apparently expired so perhaps it is time to update this posting.
I'm not sure whether addressing multiple returns or multiple criteria is more important but building an INDEX formula with the newer AGGREGATE¹ function supplying the matching row numbers can easily handle both.
The AGGREGATE function produces cyclic calculation much as the SUMPRODUCT function does but also suffers from calculation lag when full column references are applied. For that reason, all lookup ranges will be dynamically built by picking one column and determining the beginning and end of the data. This column's end row will be used for all other lookup columns as the end point of their own columns.
The primary lookup column on Sheet2 (see sample data image below) is column A; e.g. [Parent Asset Tag]. The values in this column are text based so the following will determine the extents of the data.
Sheet2!$A$1:INDEX(Sheet2!$A:$A, MATCH("zzz", Sheet2!$A:$A))
If the [Parent Asset Tag] column had been numeric, this formula would be,
Sheet2!$A$1:INDEX(Sheet2!$A:$A, MATCH(1E+99, Sheet2!$A:$A))
Sheet 2 - All assets:
Sheet1 - List of PC's
The AGGREGATE function returns the appropriate row number to INDEX using its SMALL sub-function (e.g. 15). To return the second, third, etc. matching row, all that is required is raising the SMALL's k parameter.
The matching row numbers are retrieved by forcing any non-matching row numbers into a #DIV/0!
error state and ignoring errors with the 6 option.
Sheet1 - Formulas
The formulas in A2 is,
=IFERROR(INDEX(Sheet2!$D:$D, AGGREGATE(15, 6, ROW(Sheet2!$A$1:INDEX(Sheet2!$A:$A, MATCH("zzz", Sheet2!$A:$A)))/(Sheet2!$A$1:INDEX(Sheet2!$A:$A, MATCH("zzz", Sheet2!$A:$A))=$B6), COLUMN(A:A))), "")
Fill right to return the second monitor listing. This is achieved by using the COLUMN function. COLUMN(A:A)
returns 1 but filled right becomes COLUMN(B:B)
which returns 2. This feeds the SMALL's k parameter.
The formulas in D6 is,
=IFERROR(INDEX(Sheet2!B:B, AGGREGATE(15, 6, ROW(Sheet2!$A$1:INDEX(Sheet2!$A:$A, MATCH("zzz", Sheet2!$A:$A)))/(Sheet2!$A$1:INDEX(Sheet2!$A:$A, MATCH("zzz", Sheet2!$A:$A))=$B6), 1)), "")
That retrieves the first [MonTag1 Tag]. Fill right to E6 to pick up the first [Serial ID]. Copy the formula from D6 using the formula bar (don't copy the cell) to F6 as,
=INDEX(Sheet2!B:B, AGGREGATE(15, 6, ROW(Sheet2!A$1:INDEX(Sheet2!A:A, MATCH("zzz", Sheet2!A:A)))/(Sheet2!A$1:INDEX(Sheet2!A:A, MATCH("zzz", Sheet2!A:A))=$B6), 2))
Change the k parameter to 2. This retrieves the second monitor's [MonTag1 Tag]. Fill right to G6 to pick up the second [Serial ID].
Nothing to it. Multiple criteria columns can be added as TRUE/FALSE statements multiplied against the denominator that produces the #DIV/0!
errors.
¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.
² The IFERROR function was introduced with Excel 2007. It is not available in earlier versions.
Compatibility with earlier Excel version can be maintained with the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint.