Multiple Matches Return To VLOOKUP

[Brunon]

I have been using the Microsoft Excel Sheet for long and has been using the excel sheet for carrying out all the work and also the details that are being feed in the Excel sheet are kept in a proper way so that no problem can be used while you are viewing the data that is present in the excel sheet. But now I wanted to pull the data of one employee from one of the worksheet to the another one so that it can be used for the display of the employees along with the designation that has been assigned. I wanted to get the knowledge of the VLOOK option that is present in the Excel sheet. Please help me out with this and give me the solution through which it can be done.

[!const]

You can try out the Array formula for the sheet that is present in the Excel sheet option. The Array is used to enter the value of the formula and then the look-up value to it. Then use the D1 for the taking the target in the excel sheet that will give you the first row of the column. Then you have to drag the formula down that will show the formula in the display and then you got to see that the below given code is shown to you.

=IFERROR(INDEX(Sheet1!$B$1:$B$20,LARGE((Sheet1!$A$1:$A$20=$D$1)*ROW(Sheet1!$A$1:$A$20),COUNTIF(Sheet1!$A$1:$A$20,$D$1)+1-ROW(A1))),"")

This is called the array formula which you need to enter in it pressing the ctrl+Shift+Enter and then press the enter key.

[Gunilla]

You can also try the option of the modifying the excel sheet then using the reference of the Excel Sheet that will be used for the updating of the excel sheet. The modifying of the excel sheet can be used for all the purpose and then the link can be used for the display of the matches that are being displayed. You can do this think by using the VLOOK option by just typing the VLOOKUP(lookup_value,table_array,col_index_num,range_lookup). Then you got to use the look-up value and write down the details of the data that you wanted it to display on the another worksheet.

[CodGuru]

Also you can make the use of the simple kind of the macro instead of using the some of the mysterious formula that is called the the latter which is required to perform the manual intervention that can be done anywhere.
And obne such Macro that you can use is given below:

Sub multiLookup()
Dim rLookupTable As Range, rResultTable As Range, r As Range
Dim n As Long
Set rLookupTable = Worksheets("sheet1").Range("a1:a100")
Set rResultTable = Worksheets("sheet1").Range("b1:b100")
lookupValue = UCase(Selection)
n = 0
For Each r In rLookupTable
   If lookupValue = UCase(r) Then
      n = n + 1
      Selection.Offset(n) = rResultTable.Cells(r.Row)
   End If
Next r
End Sub

This code can be used for the select set-up of the look-up and also the set-up of the macro can be done in it.
For the setting of the macro you got to do the following down set-up:
And also in the Excel you got to press alt+F11 and then you got to open the VBA window.
Then Click Insert and then the Module, and after that copy and paste then above macro into the editing pane.
After that you got to Modify the Worksheets and then Range for the rLookupTable and rResultsTable.

[void]

And also if the above formula is not working for you then you can also try some additional method that is based on to the similar principles as the one which has been described above. And if the manual intervention is not working for you’re issue then the coding can provide you the baseline to provide you with the audacity of using the below code along with its functionality.

Option Explicit
Sub Multilookup2()
Dim Lastrow As Long, lastcol As Long
Dim n As Long, x As Long
Dim SrcSht As Object, DstSht As Object
Dim LookUprange As Range, c As Range
Set SrcSht = Sheets("Sheet1")
Set DstSht = Sheets("Sheet2")
Lastrow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set LookUprange = SrcSht.Range("A1:A" & Lastrow)
For Each c In LookUprange
If WorksheetFunction.CountIf(DstSht.Range("1:1"), c.Value) = 0 Then
n = 1
lastcol = DstSht.Cells(1, Columns.Count).End(xlToLeft).Column + 1
DstSht.Cells(1, lastcol) = c.Value
For x = c.Row To Lastrow
If UCase(SrcSht.Cells(x, 1)) = UCase(c.Value) Then
DstSht.Cells(1, lastcol).Offset(n) = SrcSht.Cells(x, 1).Offset(, 1)
n = n + 1
End If
Next
End If
Next
End Sub

This should work for you if you are a coder freak as you have specified your query with the par specification that requires the expert coding trend to view the issue and get solved its problem.

[Badrunath]

And if your trying out a simple match that is being applied between the two tables then you have to see the target that has been given in the sheet 2 cell B2 and then try out the similar formula in the Sheet 2 cell C2 and then copy the content as required. And also an exceptional array formula can also be created just by pressing the keys of Ctrl+Shift+Enter whoch is used for the entry of the formula and in the same way you come to notice the curly braces of the one which is given down “{=<formula>}”
Then you get to write the formula that is given below:

=If(Countif(Sheet2!$B$1:$B$2000,$B$1)<row(B1),"",Index(Sheet2!C$1:C$2000,Small(IF(Sheet1!$B$1:$B$2000=$B$1.