Forum Discussion

dls5u's avatar
dls5u
Copper Contributor
May 30, 2025
Solved

VLookup + multiple answers

I have created a spread sheet of client names and numbers and am using VLOOKUP to bring up information.  The lookup is by last name and there are instances where there are more than one in the list. ...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jun 05, 2025

    This

    =FILTER(Providers!A2:G150,Providers!A2:A150=B6)

    might work.

    It's very similar to what Harun24HR​ has already suggested. The only difference is that FILTER uses e.g. Providers!A2:G150 instead of e.g. A.:.B.

Harun24HR's avatar
Harun24HR
Bronze Contributor
Jun 01, 2025

While OliverScheurich​ already shown how to filter using legacy array formula, you can also do it by AGGREGATE() function without enter the formula as array entry. Try-

=IFERROR(INDEX($A$2:$B$15,AGGREGATE(15,6,ROW($1:$14)/($A$2:$A$15=$G$2),ROW(1:1)),COLUMN(A$1)),"")

Also with Microsoft-365 you can easily use FILTER() function.

=FILTER(A.:.B,A.:.A=G2,"")

See the attachments (taken from OliverScheurich​ answer).

 

Resources