Excel help needed to query between 2 sheets

No attachment.  Could you also "dummy up" a few manual examples of what you expect for output?

Attached sorry
Sample-excel.xlsx

If I am understanding this correctly, you can accomplish this using VLOOKUP.  This will check Column "C" in sheet1 with Column "C" in sheet2.  If they match, it will pull the value from Column "D" in sheet2 into Column "E" in sheet1.  The following formula needs to go into Column "E" Row "1" in sheet1:

=VLOOKUP(C1,Sheet2!C:D,2,FALSE)

You can then copy that down the entire column and the C1 value will change to match each cell accordingly.  

If you were thinking something else, let me know.

Brad

@Brad - that won't address the need to concatenate multiple matches in Column E.  VB will be required.  Not my forte, but hopefully clarifies the request.

1. The names are wrongly placed

2. I only get one name in the cell. There are cases where there will be 2 or more and i want them all into a single cell

Ok, I think I understand the situation now.  Can you attach your example file?  I would like to take a look and just make sure I have a full understanding.

Brad

This should be able to do what you are looking for:

VBA Code:
Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
  If Search_in_col.Cells(i, 1) = Search_string Then
    result = result & " " & Return_val_col.Cells(i, 1).Value
  End If
Next
Lookup_concat = Trim(result)
End Function

To add this function:
Press Alt-F11 to open visual basic editor
In the Insert Menu, click Module
Copy and paste the above vba code
Exit visual basic editor

Source: http://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/

You will need to use the following formula in the in Column "E" Row "1" of Sheet1:

=Lookup_concat(C1,Sheet2!C:C,Sheet2!D:D)

You can then paste that formula down the sheet and it will update accordingly.

Let me know if you have any other issues.

Brad

Perfect
Can i have a coma delimiter between each name

Yes.  The code will need to be modified.  It is currently pulling the space from:

result = result & " " & Return_val_col.Cells(i, 1).Value

If you want it to just be a comma, change it to:

result = result & "," & Return_val_col.Cells(i, 1).Value

If you want it to be a comma and a space, change it to:

result = result & ", " & Return_val_col.Cells(i, 1).Value

Brad

Used this
result = result & ", " & Return_val_col.Cells(i, 1).Value
But a coma is created before the word as well and one names where there is just 1

This is, admittedly, a "guess" (and no points please), but I think this might work?

Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
If Search_in_col.Cells(1.1) = Search_string Then
   result = Return_val_col.Cells(1,1).Value
   For i = 2 To Search_in_col.Count
     If Search_in_col.Cells(i, 1) = Search_string Then
       result = result & ", " & Return_val_col.Cells(i, 1).Value
     End If
   Next
End If
Lookup_concat = Trim(result)
End Function

Ah no....disregard.  That patently won't work as it presumes that the first match must be found in Cell(1,1).

How about this:

Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
result = ""
   For i = 1 To Search_in_col.Count
     If Search_in_col.Cells(i, 1) = Search_string Then
       If result = "" Then
         result = Return_val_col.Cells(i,1).Value
      Else
       result = result & ", " & Return_val_col.Cells(i, 1).Value
       End If
     End If
  Next
Lookup_concat = Trim(result)
End Function

ah no...disregard

 was directed at my "guess".

@bsharath: In your example, Type (Column B) also appears to be relevant?  As currently written, the code only checks one condition (a match on Column C), and will return a result of "Ramke, Samuel" in Cell E9, which isn't supported by the data in Sheet2.  I think you will need the conditions to check for a match of both Column B and Column C, yes?

This modification satisfies both conditions in my testing:

Function Lookup_concat(Search_string1 As String, _
  Search_in_col1 As Range, Search_string2 As String, _
  Search_in_col2 As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
Results = ""
For i = 1 To Search_in_col1.Count
  If Search_in_col1.Cells(i, 1) = Search_string1 And Search_in_col2.Cells(i, 1) = Search_string2 Then
    If result = "" Then
      result = Return_val_col.Cells(i, 1)
    Else
      result = result & ", " & Return_val_col.Cells(i, 1).Value
    End If
  End If
Next
Lookup_concat = Trim(result)
End Function

Select allOpen in new window

I'd also recommend changing the formula in column E.  Currently, it checks the entire column, which is slow.  Instead, I'd recommend limiting the range of values checked:

=Lookup_concat(B4,Sheet2!$B$1:$B$25,C4,Sheet2!$C$1:$C$25,Sheet2!$D$1:$D$25)

In this example, the check is limited to just rows 1-25 on Sheet2 and is considerably faster.  

Optimally, you would also use Named Ranges and make this a dynamic range (i.e., sized to the amount of data in the respective columns).  This would allow you to grow Sheet2 data without adusting the formulas in Column E.

See here for discussion on Dynamic Named Ranges.

In practice, your formula in E would then be something like:

=Lookup_concat(B2,Type,C2,Hostname,Users)

Thanks
I just need "C" checked

Understood.  I would still recommend that you limit the ranges being checked in the Column E formulas.  It's quite slow as originally presented.

Also realized that I missed adding the link to the discussion on Dynamic Named Ranges.  See the following:

Link to Dynamic Range Introduction and practical application example