returning multiple results from a vlookup onto single row

[ellison]

Hi,


is there a way to do a vlookup between 2 different lists where the "multiple" hits are returned - rather than only the first one?


(we were using the vlookup, but it only gives us the "first" reference that it looks up)


What we'd like is for the 1st instance to be put in Col B, the second instance in Col C, the 3rd in Col D etc


Just to add to the complications(!), we are trying to do this using a partial lookup eg =vlookup("*"&A1&"*",table array,col_index_sum,FALSE)


EG

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]List 1[/TD]
[TD]Info from Lookup A[/TD]
[TD]Info from Lookup B[/TD]
[TD]Info from Lookup C[/TD]
[/TR]
[TR]
[TD]post[/TD]
[TD]red[/TD]
[TD]yellow[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]gate[/TD]
[TD]white[/TD]
[TD]black[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]List 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]posts[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]posted[/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]post[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]med gate[/TD]
[TD]white[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]large gate[/TD]
[TD]black[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Kamolga]

If you paste this in a vba Module (right click sheet name, click view code, then insert menu and chose module), you can then use nth_Occurence formula as any standard formula in Excel.

[TABLE="width: 1789"]
<tbody>[TR]
[TD]Public Function Nth_Occurrence(range_look As Range, find_it As String, occurrence As Long, offset_row As Long, offset_col As Long)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]'User define function to use a 1st,2nd,3rd,... occurence in an array[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]'=Nth_Occurrence(range_look;find_it;occurrence;offset_row;offset_col)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Dim lCount As Long[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Dim rFound As Range[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]    Set rFound = range_look.Cells(1, 1)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]        For lCount = 1 To occurrence[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]            Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]        Next lCount[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]'refer to a row or column (positive or negative) related to the found occurence[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]    Nth_Occurrence = rFound.Offset(offset_row, offset_col)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]End Function[/TD]
[/TR]
</tbody>[/TABLE]

where =Nth_Occurrence(range_look;find_it;occurrence;offset_row;offset_col) range_look is the list your are going to look in, find_it is the value so "post", occurence is 2 when you want the second (2nd column) or 3 for 3rd, offset_row is 0 as the color you want is on the same row and offset_column is 1 as it is 1 column to the right of post.

 

[ellison]

Wow, that looks amazing! But I'm getting an error message = "there's a problem with this formula" [Not trying to type a formula? When the first character is an equal (=) or minus dign(-), excel think it's a formula...]

Just to recap:
I've created a file called nth_occurrence_macro and saved it as macro enable and inserted the code as vba Module
Using the info above, I'm going to enter this formula in cell B2 to see the 1st occurrence of cells that contain "post"

So I've substituted in:

range_look = A6:B10
find_it = B2
occurrence = 1
offset_row = 0
offset_col = 1

Which gives me:
=Nth_Occurrence(A6:B10;A2;2;0;1)

BUT when I type that into B2, an error message pops up and it highlights the B10.

Am I doing something wrong?!

 

[ellison]

When I was creating the vba Module, I literally copied and pasted the code in as it stands...
I'm wondering if instead I should have substituted in the range_look for A6:B10 for and so on?

 

[AhoyNC]

Here is a formula option if you are interested.
Copy formula across and down as needed.

Excel Workbook
	A	B	C	D
1	posts	red
2	posted	yellow
3	posta	green
4	med gate	white
5	large gate	black
6
7
8	post	red	yellow	green
9	gate	white	black
Sheet

 

[ellison]

The formula option works beautifully, many thanks indeed!!!!!

 

[ClerkBot77]

I have a similar type of chart that I would like to be able to have just the non-zero results populate in a single column. I have most of my reporting page set where I can switch from person to person, just need this last piece. This thread was the closest I could find to assistance.

[TABLE="width: 500"]
<tbody>[TR]
[TD] [TABLE="class: grid, width: 500"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:5339;width:110pt" width="146"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:2377; width:49pt" width="65" span="7"> </colgroup><tbody>[TR]
[TD="width: 146"]NAME[/TD]
[TD="width: 102"]TITLE[/TD]
[TD="width: 102"]HIRE DATE[/TD]
[TD="width: 65, align: right"]1/1/2019[/TD]
[TD="width: 65, align: right"]1/2/2019[/TD]
[TD="width: 65, align: right"]1/3/2019[/TD]
[TD="width: 65, align: right"]1/4/2019[/TD]
[TD="width: 65, align: right"]1/5/2019[/TD]
[TD="width: 65, align: right"]1/6/2019[/TD]
[TD="width: 65, align: right"]1/7/2019[/TD]
[/TR]
[TR]
[TD]PERSON A
[/TD]
[TD]PACKING[/TD]
[TD]9/15/2015[/TD]
[TD]A[/TD]
[TD]LS14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]LS16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PERSON B[/TD]
[TD]PACKING[/TD]
[TD]10/10/2018[/TD]
[TD="align: right"]0[/TD]
[TD]X[/TD]
[TD]LL61[/TD]
[TD]LL11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PERSON C[/TD]
[TD]PACKING[/TD]
[TD]11/15/2017[/TD]
[TD]A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]LS16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'd like to be able to get the text to populate.

**
Sorry , my chart image didn't post originally.

 

[AhoyNC]

Maybe something like this.
You will need Excel ver. 2010 or later for this formula. If you have a earlier ver. of Excel let me know.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	NAME	TITLE	HIRE DATE	1/1/2019	1/2/2019	1/3/2019	1/4/2019	1/5/2019	1/6/2019	1/7/2019
2	PERSON A	PACKING	9/15/2015	A	LS14	0	0	LS16	0	0
3	PERSON B	PACKING	10/10/2018	0	X	LL61	LL11	0	0	0
4	PERSON C	PACKING	11/15/2017	A	0	0	0	LS16	0	0
5
6
7	PERSON B
8	X
9	LL61
10	LL11
Sheet

 

[Aladin Akyurek]

@ ClerkBot77

Looks like an ISTEXT would be appropriate...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	NAME	TITLE	HIRE DATE	1/1/2019	1/2/2019	1/3/2019	1/4/2019	1/5/2019	1/6/2019	1/7/2019		person b
2	PERSON A	PACKING	9/15/2015	A	LS14	0	0	LS16	0	0		X
3	PERSON B	PACKING	10/10/2018	0	X	LL61	LL11	0	0	0		LL61
4	PERSON C	PACKING	11/15/2017	A	0	0	0	LS16	0	0		LL11
5
Sheet1

In L2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($D$2:$J$4,MATCH(L$1,$A$2:$A$4,0),SMALL(IF(ISTEXT(INDEX($D$2:$J$4,MATCH(L$1,$A$2:$A$4,0),0)),COLUMN($D$2:$J$4)-COLUMN($D$2)+1),ROWS(L$2:L2))),"")

 

[ClerkBot77]

This looks like it works for my sheet.

As you can see with the small example, I have the columns as individual dates. What I'm trying to chart is also separated by quarter. (jan-mar, apr-jun etc..) I thought I had it my modifications to your formula set but it looks like too many arguments.

This is an obnoxious sheet, so it's probably not going to work posting it on this thread. but here is what I modified to your formula:

=IFERROR(INDEX(INDEX(if(B8="1st",qtrone,if(B8="2nd",qtrtwo,if(B8="3rd",qtrthree,if(B8="4th",qtrfour," ")))),MATCH($B$6,emp,0),0),AGGREGATE(15,6,(COLUMN(if(B8="1st",_1_1_3_31,if(B8="2nd",_4_1_6_30,if(B8="3rd",_7_1_9_30,if(B8="4th",_10_1_12_31," "))-COLUMN($D$1)+1)/(INDEX(if(B8="1st",qtrone,if(B8="2nd",qtrtwo,if(B8="3rd",qtrthree,if(B8="4th",qtrfour," ")))),MATCH($B$6,emp,0),0)<>0),ROWS($B$39:B39))),"")

I tried to next some "if" statements referencing a cell where the qtr was selected and named the quarters qtrone, qtrtwo etc.. emp is the employee list data portion the "_1_1_3_31" reference the date ranges.

Would it be better if I had the quarter data on separate tabs?