Import data from one excel worksheet to another

[arjun rantu]

I require to import data from one excel worksheet to one more to exhibit all employee names from a exacting company.
For example:

A - B
Amb - Kali
Mart - Fred
M-Mart - Abi
Amb - Kali
Mart - Fred
M-Mart - Abi
Amb - Kali
Mart - Fred

What formula can I make use of in another sheet that is in sheet2 to exhibit a list of all Amb employees from sheet1?

[void]

Attempt this ARRAY formula on sheet 2. Enter the formula and place your lookup value of Amb in E1. Drag the formula down until it start creating blanks.
=IFERROR(INDEX(Sheet1!$B$1:$B$30,LARGE((Sheet1!$A$1:$A$30=$E$1)*ROW(Sheet1!$A$1:$A$30),COUNTIF(Sheet 1!$A$1:$A$30,$E$1)+1-ROW(A1))),"")
This is an array method that must be entered by pressing CTRL+Shift+Enter and not only Enter. If you do it accurately in that case Excel will place curly brackets {} around the formula. You cannot type this manually. If you edit the formula you have to enter it once more with CTRL+Shift+Enter.

[CodGuru]

I also want to do the same and don’t get any formula. I just see your post but I am not extremely higher. Can you please put in plain words to me what the dissimilar parts of the formula are? Obviously the data I am using here is not genuine. While I attempted adjusting the sheet or cell references to make use of in my genuine workbook it did not work. I think I will have an enhanced possibility if I know what I am working with.

[Gunilla]

Just try the following formula.
=IFERROR (formula,"")
The external IFERROR merely makes it revisit blanks while it runs out of matches. Now in every case I will bold the bit of the formula. Here I am talking about the primary bold bit are the values we are annoying to depart. Sheet1 is the name of the sheet which is followed by a “!” and $B$1:$B$30 is the range.
=IFERROR(INDEX(Sheet1!$B$1:$B$30,LARGE((Sheet1!$A$1:$A$20=$E$1)*ROW(Sheet1!$A$1:$A$30),COUNTIF(Sheet1!$A$1:$A$30,$E$1)+1-ROW(A1))),"")
Now at this time 3 bits of the formula are underlined and this is the range which encloses the lookup values. This range A1:A30 can be any volume but have to be the similar size as the range B1:B30

[!const]

I advise by means of an easy macro in its place of a few strange formulas, since the latter needs manual interference in any case that is, predetermining the number of consequences or deciding how much times to copy down the formula. One such macro:

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

Place the names of the search for values keen on a row of cells in Sheet2, e.g. A1, B1, C1, and so on. Choose every lookup value, push alt+F8, and run the macro. The multiple results will come into view in the column beneath the lookup value.