'Return a value in a range if a dropdown =

I want to populate the adjacent cells in a sheet depending on the drop down box value.

In the workbook, I have a DATA sheet listing contact information, name, phone & email.

In the main sheet, you can select from the dropdown list a company name and then I would like to populate the adjacent cells with the information from the data sheet.

What would be the best method?

Cheers

Solution 1:^[1]

Formula in B3 I used-

=IF(A3="","",INDEX($I$3:$K$7,MATCH($A$3,$H$3:$H$7,0),COLUMN(A$1)))

With EXCEL-365 you can use FILTER() function.

=FILTER(I3:K7,H3:H7=A3,"")

Solution 2:^[2]

i solve the problem with an event

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim strName As String
  Dim lngRow As Long, lngRowMax As Long
  Dim rngFind As Range


  If Target.Column = 1 Then

   lngRowMax = Range("F" & Rows.Count).End(xlUp).Row
   For lngRow = 2 To lngRowMax

    If Range("F" & lngRow).Value = Target.Value Then
     strName = strName & "," & Range("G" & lngRow).Value
    End If

   Next lngRow

   strName = Mid(strName, 2)


   Target.Offset(0, 1).Validation.Delete
   Target.Offset(0, 1).Validation.Add Type:=xlValidateList, Formula1:=strName

  End If

  If Target.Column = 2 Then
   Set rngFind = Range("G:G").Find(what:=Target.Value, lookat:=xlWhole)
   If Not rngFind Is Nothing Then
    Range("C" & Target.Row).Value = rngFind.Offset(0, 1).Value
   End If
  End If

End Sub

BG Bernd

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Harun24hr
Solution 2	user18083442

'Return a value in a range if a dropdown =

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]