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. ...
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.
=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.
Excellent! That's beautiful. Thanks