Extract names from a list that have a certain value

**mtouhig** · 07-16-2020, 02:51 AM

Hi. I have a list of names, each with 6 numbers associated with their name.

I wish to enter a number (into I4 and Excel return all names in the list that have that number, into J:, K:, L: and soon. Or, if easier, all names into just J:
Most numbers will have more than one name.

I'm sure this is easy, but I'm a real novice with Excel and looking to improve with online courses!

Many thanks for any help.

**alansidman** · 07-16-2020, 03:05 AM

A vba solution

Please Login or Register  to view this content.

How to install your new code

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

**mtouhig** · 07-16-2020, 03:19 AM

Thank you for the quick reponse, Alan. But surely there must be a fairly easy way in Excel using functions and formula - I really don't want to be using VBA if I can avoid it (I know even less about that than Excel

)

**bebo021999** · 07-16-2020, 03:30 AM

Formula solution:
In J4:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

**mtouhig** · 07-16-2020, 03:58 AM

Thank you Bebo. I'm afraid that doesn't work.

I wish to enter a number, if any name in the list has that number in their list, I want Excel to return the name(s).

For example, if I was to enter 23, Excel should return Alex, Amy, Dave W

**AliGW** · 07-16-2020, 04:01 AM

It works - it does what you said you wanted:

Excel 2016 (Windows) 32 bit

	I	J	K	L
3	Number	Names
4	3	Amy	Dave Kelly	Dave Kelly Mary
5	6	Alex	Dave Kelly Diane	Dave Kelly Mary
6	11	Alex	Autar	Dave W

Sheet: Sheet1

Did you remember to drag copy it across and down???

Excel 2016 (Windows) 32 bit

	I	J	K	L	M
3	Number	Names
4	23	Alex	Amy	Dave Kelly Diane	Dave W
5	6	Alex	Dave Kelly Diane	Dave Kelly Mary
6	11	Alex	Autar	Dave W

Sheet: Sheet1

**mtouhig** · 07-16-2020, 04:23 AM

Sorry! I must be missing someting here

I have copied the formula that Bebo suggested into J4. I understand what an array formula is.

But if I enter a value to test, surely the formula is overwritten?

Forgive me, but as I say, my Excel knowledge is novice to dsay the best.

Ali - what you have attached is exactly what I am after. Thank you.

**AliGW** · 07-16-2020, 04:29 AM

You shouldn't need to enter it as an array formula as you have Office 365.The formula goes into J4 and is copied across to the right and down. Test values go into column I, not column J.