Forum Discussion
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. Is there a way to display the info both ( or all three or whatever?)
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.
7 Replies
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).
=IFERROR(INDEX(A$2:A$15,SMALL(IF($A$2:$A$15=$G$2,ROW($A$2:$A$15)-1),ROW(A1))),"")An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. You can select the client name from the dropdown in cell G2 in the sample file. The formula is in cell D2 and filled across range D2:E14.
If you have access to the FILTER function the solution would be easier by far. Since you are asking for VLOOKUP i assume that you are working with legacy Excel such as Excel 2013.
Apparently, this is Excel 365. I knew about VLOOKUP from previous versions. I'm trying to set up a SEARCH by inputting a name on one page that looks up the name on a second page and returns the associated information back onto the first page.
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.
VLOOKUP only returns the first match. To get all matches, use this (Excel 365+):
=TEXTJOIN(", ", TRUE, FILTER(B2:B100, A2:A100=D1))
This returns all values from column B where column A matches the name in D1.
Thanks, that does show the different names but I have several columns that I'm trying to pull information for those names. Phone # from column C, Fax from Column D, Practice name from Column E, etc. and display those.
