VLookup & Concatenate - What is wrong with my formula?

[blillie11]

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?

 

[EddyVilla]

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

 

[blillie11]

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

Thanks for your reply 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.

 

[blillie11]

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>

 

[azumi]

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

 

[marc j]

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