Look up

[Saviour198]

I have customer lists; customer name, location, remarks and type of coffee they prefer. I want a formula which when I am preparing a delivery list and I type the customer name it should return their location, remarks and type of coffee they prefer on a roll.

 

[Kaper]

So let's say your current customer name is in cell G2
in H2 write formula:

=vlookup(G,$A$2:$G$100,COLUMN(B1),0)

copy this formula right (and if needed - down)
adjust $G$100 to your real loast cell (may be with few extra white cells for growning database situation.

BTW. Knowing Excel version could lead to more elegant solution (anyway this is not very bad I think)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have one of the newer version you could use

=xlookup(g2,a$2:a$100,b$2:d$100)

 

[Logit]

Here is a VBA solution :

Paste in the WorkSheet leverl module :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
  'Do nothing if more than one cell is selected
  If Target.Count > 1 Then Exit Sub
  'Is C15 selected?
  If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub
  'Search option range for value from G1
  Set R = Range("CustName").Columns(1).Find(Range("G2"), LookAt:=xlWhole)
  'Found?
  If R Is Nothing Then
    MsgBox "Not found"
  Else
    'Show the value
    MsgBox "LOCATION : " & R.Offset(0, 1) & vbCrLf & vbCrLf & "REMARKS : " & R.Offset(0, 2) & vbCrLf & vbCrLf & "COFFEE : " & R.Offset(0, 3)
  End If
End Sub

I utilized cell G2 for Customer Name entry :

 

[Saviour198]

So let's say your current customer name is in cell G2
in H2 write formula:

=vlookup(G,$A$2:$G$100,COLUMN(B1),0)

copy this formula right (and if needed - down)
adjust $G$100 to your real loast cell (may be with few extra white cells for growning database situation.

BTW. Knowing Excel version could lead to more elegant solution (anyway this is not very bad I think)

 

[Saviour198]

Thank you. I will try and also confirm the excel version.