Retireve Multiple Vlookup Values in a single Cell on Partial Match

[venkyzrocks]

Hi Guys ,

Please refer to the the sheet below. I have used =VLOOKUP("*"&$C$2&"*",$A$1:$B$4,2,FALSE) to get the value in the Final Value column based on Partial Match. But the expected value that I need is R1, R3 , R3 . This is just an eg need to to work for a column which has more than 10000 rows.

So how would I do a partial match vlookup or using the index and match function and retrieve multiple values in a single cell

COMMENT <colgroup><col width="707"></colgroup><tbody> </tbody>	Value	Values To Be Looked Up Using Partial Match	Final Value Got
Thanks!	R1	Thank	R1
Thank	R2
Thank You	R3

<tbody>
</tbody>

Thanks ,
Venkatesh

 

[Sal Paradise]

Since partial matches require array formulas, and array formulas don't play well with concatenate, it gets ugly, like this:

Excel 2003
	A	B	C	D	E
1	Comment	Value		Match:	Thank
2	Thanks!	R1		Matches:	3
3	Thank	R2		Values:	R1,R2,R3
4	Thank You	R3
Sheet1

Cell Formulas
Range	Formula
E2	{=SUM(IF(NOT(ISERROR(FIND(E1,A2:A4))),1))}
E3	{=IF(1<=$E$2,INDEX(B:B,SMALL(IF(NOT(ISERROR(FIND($E$1,$A$2:$A$4))),ROW($A$2:$A$4)),1),),"")&","&IF(2<=$E$2,INDEX(B:B,SMALL(IF(NOT(ISERROR(FIND($E$1,$A$2:$A$4))),ROW($A$2:$A$4)),2),),"")&","&IF(3<=$E$2,INDEX(B:B,SMALL(IF(NOT(ISERROR(FIND($E$1,$A$2:$A$4))),ROW($A$2:$A$4)),3),),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

So either it's best to put them in separate cells (one per match), or to use VBA to create a user-defined function.

 

[venkyzrocks]

Hi,

Thanks for helping but I wonder how you are getting three I tried the same formual in excel 2010 and am getting 1 and R1, , as the 2nd ouput. Also this was an example .I don't mind putting it in different cells .But I have more than 50 values to lookup so I'm not sure if this is a feasible solution. Please let me know I'm doing something wrong.

Thanks,
Venkatesh

 

[venkyzrocks]

I got how you got 3 and R1,R2,R3 ..But what If I have to lookup for multiple lookup values ? and still getting values R1, R2 whatever row value they belong to .Even if I get in different cells it should be fine

 

[Sal Paradise]

I would set the sheet up like this:

Excel 2003
	A	B	C	D	E	F	G	H	I	J
1	Names	Values		Lookups	Matches	Value 1	Value 2	Value 3	Value 4	Value 5
2	CCvhtt	7		AA	5	AAabjk	AApakq	AAsqtc	AAhwpq	AAwxlw
3	BBmcyj	33		AB	4	ABfehz	ABoaxm	ABlfaa	ABszgv
4	ABszgv	4		AC	3	ACdjjz	ACriek	ACuxrq
5	BAvmfi	41		BA	5	BAxcej	BAqvkv	BAjtit	BAgren	BAvmfi
6	ABlfaa	58		BB	3	BBptmf	BBnasw	BBmcyj
7	ABoaxm	2		BC	0
8	ACuxrq	68		CA	1	CArchd
9	ABfehz	15		CB	2	CBwhpu	CBkunk
10	BAgren	1		CC	4	CCfdwa	CCsflq	CCuqgm	CCvhtt
11	CCuqgm	53
12	BAjtit	49
13	CArchd	4
14	CCsflq	15
15	ACriek	68
16	BAqvkv	29
17	ACdjjz	22
18	AAwxlw	1
19	BAxcej	23
20	CBkunk	74
21	AAhwpq	35
22	AAsqtc	14
23	CBwhpu	66
24	AApakq	49
25	CCfdwa	48
26	BBnasw	2
27	BBptmf	67
28	AAabjk	20
Sheet1

Cell Formulas
Range	Formula
E2	{=SUM(IF(NOT(ISERROR(FIND(D2,$A$2:$A$28))),1))}
F2	{=IF(COLUMN(A1)<=$E2,INDEX($A:$A,LARGE(IF(NOT(ISERROR(FIND($D2,$A$2:$A$28))),ROW($A$2:$A$28)),COLUMN(A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Adjust the number of columns if you have more than 5 matches. You can concatenate all the entries at the end if you'd like to.

 

[muneshnaagar]

Hi Sal,

I came to this thread looking out for a solution to my problem and this is exactly what I was looking for. Its just that I am not able to get more than Value1. Would you please help me understand how to get more than one matches. I am quite new to array formulas.

Thanks in advance.

Regards

 

[Eric W]

Welcome to the MrExcel forum.

First, just a an FYI, it's better to start your own thread than to add on to a thread that's 6 years old. It was just blind luck that I happened to notice it. Sal hasn't posted anything in years. If you open a new thread, it shows up on the "Zero Reply Posts" button, which a lot of people look at.

Next, assuming your layout is pretty much the same as Sal's, the formulas should work. Copy his F2 formula (without the {} ), select F2 on your sheet, enter the formula in the formula bar, then hold down the Control and Shift keys, then press enter. Then copy the cell and paste it as needed to the right, then down the columns.

A good test is to start with the E2 formula, which is also an array formula. If you get the right values for that, then you're on the right track.

Hope this helps!

 

[muneshnaagar]

Thanks for your reply Eric. Will follow that. But I want to get values in "column G", "column H" and so on. I am not able to get the second or third possible match. How do i get that, I you could help?

 

[Fluff]

Now posted here https://www.mrexcel.com/forum/excel...-possible-matches-same-range.html#post5212628

All reply should go to the new thread