For every instance of a match in column A, retrieve the data from column B

[videozvideoz]

I have a table on Sheet2 similar to the one below:

Craig 123
John 234
Dan 345
Craig 876
John 765
Dan 654
Craig 466

I want to create a lookup on Sheet1 in Excel that searches column A on Sheet2 for 'Craig' and retrieves all the data in column B on Sheet2 in order so the end result looks like this:

Craig 123
Craig 876
Craig 466

I've tried allsorts for the past 2 hours and not really got anywhere.

Thanks,

JB

 

[hiker95]

videozvideoz,

Welcome to the MrExcel forum.



Sample raw data in worksheet Sheet2:

Excel Workbook
	A	B
1	Names	Values
2	Craig	123
3	John	234
4	Dan	345
5	Craig	876
6	John	765
7	Dan	654
8	Craig	456
9
Sheet2

Sample worksheet Sheet1, with the search name in cell A2:

Excel Workbook
	A	B	C
1	Name	Sheet2 Values	3
2	Craig	123
3		876
4		456
5
6
7
8
9
10
Sheet1

Cell C3 contains a counter of how many times the entry in cell A2, Craig is found in worksheet Sheet2, column A.



You will have to copy the below formula into cell B2, confirmed with CTRL + SHIFT + ENTER (not just with ENTER), and copy the formula down the number of times that cell C3 shows:


=IF(ISERROR(INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$A$2:$A$100=$A$2,ROW(Sheet2!$B$2:$B$100)-ROW(Sheet2!$B$2)+1),ROWS(Sheet2!$B$2:B2)))),"",INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$A$2:$A$100=$A$2,ROW(Sheet2!$B$2:$B$100)-ROW(Sheet2!$B$2)+1),ROWS(Sheet2!$B$2:B2))))

 

[Armando Montes]

http://chandoo.org/wp/2011/04/01/march-2011-is-best-month-ever-and-other-news/
Unlock the hidden MVLOOKUP formula

We all gripe about how VLOOKUP returns only first match. We want to get all the matching results. But how?!?
Thankfully, there is a formula called MVLOOKUP that Microsoft did not want you to find. So they hid it deep inside Excel. But just like everything else, we can unlock the MVLOOKUP formula too.
Follow this simple procedure:

1. In the cell where you want MVLOOKUP formula, just type =M V L O O K U P(value you are looking for, table of data, column number, false) and press CTRL+SHIFT+Enter.
2. Note1: You must include spaces between each letter in the MVLOOKUP.
3. Note2: You must use the CTRL and SHIFT keys located on the left side of keyboard only.
4. Note3: This works in Excel 2007 and Windows machines only.
5. When you press CTRL+SHIFT+Enter, you will see that excel has returned an array of values corresponding the value you are looking for.

If the formula doesn’t work:

If you are getting #NAME or some other error try quitting Excel and restart it. But this time, when clicking on Excel icon, hold Alt key.

 

[videozvideoz]

videozvideoz,

You will have to copy the below formula into cell B2, confirmed with CTRL + SHIFT + ENTER (not just with ENTER), and copy the formula down the number of times that cell C3 shows:


=IF(ISERROR(INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$A$2:$A$100=$A$2,ROW(Sheet2!$B$2:$B$100)-ROW(Sheet2!$B$2)+1),ROWS(Sheet2!$B$2:B2)))),"",INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$A$2:$A$100=$A$2,ROW(Sheet2!$B$2:$B$100)-ROW(Sheet2!$B$2)+1),ROWS(Sheet2!$B$2:B2))))

Thanks for the welcome and an even bigger thanks for sharing your expertise! That piece of code is amazing and does the job exactly. Thanks so much for your help!

VideozVideoz

 

[Aladin Akyurek]

Thanks for the welcome and an even bigger thanks for sharing your expertise! That piece of code is amazing and does the job exactly. Thanks so much for your help!

VideozVideoz

B2 should be...

Control+shift+enter, not just enter:

=IF(ROWS($B$2:B$2)<=$C$1,INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$A$2:$A$100=$A$2,ROW(Sheet2!$B$2:$B$100)-ROW(Sheet2!$B$2)+1),ROWS(Sheet2!$B$2:B2))),"")

which avoids calculating the expensive expression twice.

In Excel 2007 and later, one could use IFERROR...

=IFERROR(INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$A$2:$A$100=$A$2,ROW(Sheet2!$B$2:$B$100)-ROW(Sheet2!$B$2)+1),ROWS(Sheet2!$B$2:B2))),"")

 

[T. Valko]

I have a table on Sheet2 similar to the one below:

Craig 123
John 234
Dan 345
Craig 876
John 765
Dan 654
Craig 466

I want to create a lookup on Sheet1 in Excel that searches column A on Sheet2 for 'Craig' and retrieves all the data in column B on Sheet2 in order so the end result looks like this:

Craig 123
Craig 876
Craig 466

I've tried allsorts for the past 2 hours and not really got anywhere.

Thanks,

JB

Try this...

Let's assume this is your data on Sheet2 in the range A2:B8.

Craig 123
John 234
Dan 345
Craig 876
John 765
Dan 654
Craig 466

In the formulas I use the following named ranges:

• Names
• Refers to: =Sheet2!$A$2:$A$8
• Values
• Refers to: =Sheet2!$B:$B

On Sheet1:

• A2 = lookup name like Craig

Enter this formula in B2. This will return the count of records for the lookup name entered in A2.

=COUNTIF(Names,A2)

Enter this array formula** in C2. This will extract the values that correspond to the lookup name in A2.

=IF(ROWS(C$2:C2)>B$2,"",INDEX(Values,SMALL(IF(Names=A$2,ROW(Names)),ROWS(C$2:C2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy down until you get blanks.