# VLookup & Concatenate - What is wrong with my formula?

#### blillie11

##### New Member
Here is my data on sheet 1:

 Part# Matching Part's (Desired Output in Row 2 ABC123?.681 =VLOOKUP(CONCATENATE(A1,'Sheet 2'!A:A,",") ABC123?.290 ABC123S.290,ABC123M.290 ABC123?.540 ABC123S.540 ABC123?.999 ABC123M.999,ABC123X.999,ABC123L.999

<tbody>
</tbody>

Here is my data on sheet 2:
 Actual Part #'s ABC123S.681 ABC123M.681 ABC123L.681 ABC123S.290 ABC123M.290 ABC123S.540 ABC123M.999 ABC123X.999 ABC123L.999

<tbody>
</tbody>

My formula isn't working. What am I doing wrong?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Hi Billie,

im not as savvy as most ppl here however i think your post needs more description, what are you trying to achieve??, your formula on the descriptions is incorrect its missing a ")" also why is there "?" on data in sheet 1.

Probably if you describe the problem a little bit deeper, someone might be able to help you out.

Eddie

Hi Billie,

im not as savvy as most ppl here however i think your post needs more description, what are you trying to achieve??, your formula on the descriptions is incorrect its missing a ")" also why is there "?" on data in sheet 1.

Probably if you describe the problem a little bit deeper, someone might be able to help you out.

Eddie

I noticed the last ")" didn't copy over when I posted my scenario but that is not the issue.

The "?" Is a wildcard in Excel but for the exact position it is in.

In my example, I'm trying to lookup data and concatenation it in sheet 2 that matches my part # in sheet 1.

OK, So I got it figured out..... almost.

This is the formula I'm using: =LookUpConcat(A1,'Sheet 2!A\$2:A\$2250,'Sheet 2'!B\$2:B\$2250,", ")

I'm using it with this UDF:

Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range, _
Optional Delimiter As String = " ", Optional MatchWhole As Boolean = True, _
Optional UniqueOnly As Boolean = False, Optional MatchCase As Boolean = False)

Dim X As Long, CellVal As String, ReturnVal As String, Result As String

If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
(ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
LookUpConcat = CVErr(xlErrRef)
Else
If Not MatchCase Then SearchString = UCase(SearchString)
For X = 1 To SearchRange.Count
If MatchCase Then
CellVal = SearchRange(X).Value
Else
CellVal = UCase(SearchRange(X).Value)
End If
ReturnVal = ReturnRange(X).Value
If MatchWhole And CellVal = SearchString Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
End If
Continue:
Next

LookUpConcat = Mid(Result, Len(Delimiter) + 1)
End If

End Function

I got it to display correctly, when I take the wildcard ("?" in this case) out of my data in cell A1. But as you can see from my data below, I need the wildcard. My actual part #'s contain different letters that correspond with their attributes (S = small, M = Med, etc.). The red text is my desired result.

Any help on getting this to work with my wildcard?

Sheet 1

 Row Column A Column B 1 ABC123?.681 =LookUpConcat(A1,'Sheet 2!A\$2:A\$2250,'Sheet 2'!B\$2:B\$2250,", ") 2 ABC123?.290 ABC123L.290 3 ABC123?.540 ABC123S.540 4 ABC123?.999 ABC123L.999,ABC123X.999

<tbody>
</tbody>

Sheet 2
 Row Column A Column B 1 ABC123S.681 ABC123S.681 2 ABC123M.681 ABC123M.681 3 ABC123L.290 ABC123L.290 4 ABC123S.540 ABC123S.540 5 ABC123L.999 ABC123L.999 6 ABC123X.999 ABC123X.999

<tbody>
</tbody>

Assumed the table data in Sheet2 ranges A1:A9

Sheet 1 in Cell A1 : Criteria

Put in Cell B2 : =IFERROR(INDEX(Sheet2!A\$1:A\$9,SMALL(IF(ISNUMBER(SEARCH(\$A\$1,Sheet2!\$A\$1:\$A\$9)),ROW(Sheet2!\$A\$1:\$A\$9)-MIN(ROW(Sheet2!\$A\$1:\$A\$9))+1),ROWS(\$1:1))),"")

and drag down until blank result

(Array Formula) need to hit CTRL-SHIFT-ENTER Button together....

or see the file https://www.dropbox.com/s/guthof583bw0p06/Partial Lookup.xlsx

Azumi

Hello and welcome,
I have a similar solution for you,
If the result you are looking is one with /// ABC123M.999,ABC123X.999,ABC123L.999 ///
I have a 2 part solution, very similar azumi's formula
Sheet1 cells a2 to a5 have the search cells
A2=ABC123?.681
B2=ABC123?.290
etc
Your dataset is on sheet2 a2:a10

Enter this array formula with CTRL-SHIFT ENTER in D2...
and down and across as many columns as needed...

=IF(COLUMNS(\$D2:D2)>COUNTIF(Sheet2!\$A\$2:\$A\$10,\$A2),"",INDEX(Sheet2!\$A\$2:\$A\$10,SMALL(IF(TRIM(RIGHT(\$A2,3))=TRIM(RIGHT(Sheet2!\$A\$2:\$A\$10,3)),ROW(Sheet2!\$A\$2:\$A\$10)-ROW(Sheet2!\$A\$2)+1),COLUMNS(\$D\$2:D2))))

Your resulting formula in B2 is just a concatenation of the columns..
=IF(D2<>"",D2,"")&IF(E2<>"",","&E2,"")&IF(F2<>"",","&F2,"")&IF(G2<>"",","&G2,"")...etc as many columns as you will need. If you need to add columns just copy paste // &IF(G2<>"",","&G2,"") // and adjust the column G to H..etc..I think you will see the pattern.

Hope that helps.

marcj

Replies
3
Views
130
Replies
0
Views
186
Replies
3
Views
267
Replies
10
Views
423
Replies
6
Views
165

1,203,224
Messages
6,054,234
Members
444,711
Latest member
Stupid Idiot

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