Vlookup with multiple Row conditions

[Vijayarc]

Hi Ninja Team

If we Vlookup the value. it will display only the first match data. However i need to display all matched data for my lookup.
i have below dynamic raw file with 1000 rows , if i vlookup sheet1 B with Sheet2 B:Q i will get first value only
" =VLOOKUP(B2,ALE!B:Q,16,0)
it is difficult to find all matched data manually update,
please help to VBA to match all possible value to the cell as expected vlookup (AQ), attached file four your reference.

Thanks in Advance

 

[Chihiro]

Version of Excel?
Office 365, 2019, 2016 etc.

 

[Khalid NGO]

Hi,

If it is okay to have result in separate cells, you can use this in AQ2 and then copy across:

=IFERROR(INDEX(ALE!$Q$2:$Q$5,AGGREGATE(15,6,1/(ALE!$A$2:$A$5=$B2)*ROW(ALE!$Q$2:$Q$5)-1,COLUMN(A1)),),"")

Regards,

 

[Chihiro]

If using Office 365.
=TEXTJOIN("-",TRUE,UNIQUE(FILTER(ALE!$Q$2:$Q$5,ALE!$B$2:$B$5=B2)))

 

[Vijayarc]

Thanks Ninja

I am using Office 2013,
the above formula not helping for me, as i mentioned earlier, i have dynamic row sheet, more that 550 to 700 rows daily in both sheets ,,
i cant fix the range only Q2 to Q5 in formula,

please help !!

 

[Chihiro]

Then I'd recommend giving bit more sample.

With Excel 2013, do you have access to Power Query (Get & Transform)? If you do, that's probably the best method.

Dynamic concatenation before Office 365, is difficult to do with formula alone.

 

[p45cal]

A Power Query solution in the attached.
Refresh the table at cell A4 of the Master sheet.
See result in cell AQ5 of the same sheet.
If you supply a more representative sample of data (not just one ECI) I'll go into more detail.

 

[Vijayarc]

HI Team

As suggest, i have attached the complete sample data sheet,data range is not fixed my original file

Please suggest any way on VBA to achieve this rather than Power query

Kindly help me - Thanks in advance

 

[Chihiro]

Why the aversion to PQ? It will be much simpler for you to maintain.

Here's something that you can do.

Sub Demo()
Dim ar, tmpAr, mData, res, y
Dim i As Long, j As Long

With Sheets("ALE")
    ar = .Range("A2:Q" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With

ReDim tmpAr(UBound(ar))
For i = 1 To UBound(ar)
    tmpAr(i - 1) = ar(i, 1) & "~" & ar(i, 2) & "~" & ar(i, 17)
Next
Erase ar

With Sheets("Master")
    mData = .Range("B2:C" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
    res = .Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value
End With

For i = 1 To UBound(res)
    y = Filter(tmpAr, mData(i, 1) & "~" & mData(i, 2), , vbTextCompare)
    With CreateObject("Scripting.Dictionary")
        For j = 0 To UBound(y)
            .Item(y(j)) = 1
        Next
        res(i, 1) = Replace(Join(.Keys, "-"), mData(i, 1) & "~" & mData(i, 2) & "~", "", , , vbTextCompare)
    End With
Next

Sheets("Master").Range("AQ2").Resize(UBound(res)) = res

End Sub

Note: If sort order of concatenated strings are important... either order the data in "ALE" sheet. Or add text manipulation logic before you place value into res(i, 1).

 

[p45cal]

In the attached files, the one postfixed v2 contains a Power Query solution while the one postfixed v3 contains a vba solution.
The v3 solution does not erase data from column AQ, it only overwrites, so if your latest list is shorter than a previous version there will be leftover values at the bottom of column AQ.

 

[Vijayarc]

Thanks u All Ninja's

Thanks again for all your time and help !!!

it saves me much time..