Retireve Multiple Vlookup Values in a single Cell on Partial Match

venkyzrocks

New Member
Joined
Oct 4, 2009
Messages
36
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Since partial matches require array formulas, and array formulas don't play well with concatenate, it gets ugly, like this:


Excel 2003
ABCDE
1CommentValueMatch:Thank
2Thanks!R1Matches:3
3ThankR2Values:R1,R2,R3
4Thank YouR3
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I would set the sheet up like this:


Excel 2003
ABCDEFGHIJ
1NamesValuesLookupsMatchesValue 1Value 2Value 3Value 4Value 5
2CCvhtt7AA5AAabjkAApakqAAsqtcAAhwpqAAwxlw
3BBmcyj33AB4ABfehzABoaxmABlfaaABszgv
4ABszgv4AC3ACdjjzACriekACuxrq
5BAvmfi41BA5BAxcejBAqvkvBAjtitBAgrenBAvmfi
6ABlfaa58BB3BBptmfBBnaswBBmcyj
7ABoaxm2BC0
8ACuxrq68CA1CArchd
9ABfehz15CB2CBwhpuCBkunk
10BAgren1CC4CCfdwaCCsflqCCuqgmCCvhtt
11CCuqgm53
12BAjtit49
13CArchd4
14CCsflq15
15ACriek68
16BAqvkv29
17ACdjjz22
18AAwxlw1
19BAxcej23
20CBkunk74
21AAhwpq35
22AAsqtc14
23CBwhpu66
24AApakq49
25CCfdwa48
26BBnasw2
27BBptmf67
28AAabjk20
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,203,521
Messages
6,055,887
Members
444,830
Latest member
Excelsmallbusinessmom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top