# Retireve Multiple Vlookup Values in a single Cell on Partial Match

#### venkyzrocks

##### New Member
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.

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

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

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.

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.

Regards

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!

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?

Replies
5
Views
569
Replies
9
Views
179
Replies
1
Views
101
Replies
7
Views
389
Replies
10
Views
348

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

### 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.

### Which adblocker are you using?

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

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