Variation of VLOOKUP that returns ALL matches, not just the first one...

Hi,
Please can anyone tell me if there is a version of the VLOOKUP which will return all matches, and not just the first one.

For example, please see the attachment.
The second sheet ('Cities') has a list of countries and cities.
The first sheet ('Country') contains the VLOOKUP function, but it will only return the first match it finds.

What function do i need to use so that what is returned is EVERY match.
e.g. If I enter 'Italy' , I want it to return 'Rome' and 'Venice' (either in adjacent cells in the same row, or in the same cell, for example).

Thanks.

Here is a solution that I have used.

Originally Posted by alansidman

Here is a solution that I have used.

Thanks - my version of excel (2010) does not have a function called MLOOKUP.

What do I do?

With the file Mlookup open, click on Alt F11. This will open the VBA section of Excel. In the left VBA Project window, click on the module, it will open the code to create the UDF (User Defined Function) Mlookup. Copy it and paste it into your personal.xlsb file or the file you wish to use it in a new module. Save and close the VBA window. This function will now be available to you in your regular excel function under the topic UDF. I have done this for you in the attached.

Alan

Edit: if you want this available to you globally, you will need to create a personal.xlsb file. Look at this link:

http://www.rondebruin.nl/personal.htm

Good luck on taking this next step in Excel.

Did you look in the file I attached. Also, if you installed it, click on the fx in the function window. Then select the section in the drop down called UDF. You should see an MLookup there. In your original file, you may have to change the file name to an xlsm file which is macro enabled. Will probably not work in an xlsx file.


Edit note: See attached. I double checked and reloaded the module. It does work in this spreadsheet.

No problem with the heavy lifting. Now that you understand, the next time will be easier. Excel is always a learning experience.

also it should be noted that if you cannot use macros this can be done like this


Countries and Cities.xlsx

@ twiggywales

As the poster has Excel 2010, they can use the AGGREGATE function, which has the SMALL function. So the resulting formula is a non array.
=IFERROR(INDEX(Cities!$B$1:$B$15,AGGREGATE(15,6,ROW(Cities!$A$1:$A$15)/(Cities!$A$1:$A$15=$A2),COLUMN(A$1))),"")

Hi anthony and welcome to the forum

I would suggest that you create your own thread, and reference this 1 in your new thread (else it could be considered hi-jacking Leicester's thread (which is a no-no lol)