Phonebook lookup in Excel

[badflyer]

Hi all,

I have a excel spreadsheet which contains all my work contact names. It is arranged by columns of as follows:

Surname | First Name | Phone | Email | Job function.

I have created a VLOOKUP on Phone, Email and Job function, such that when i enter the persons Surname in a assigned cell, it will return the persons Phone, Email and job function. This works great, until i come across instances where two Surnames are the same.

I need some VBA code to prompt the user that there is more than one instance of this surname, and then offer the user the opportunity to enter a First name, such that the correct person can be located. What is the best way to do this guys? Many thanks

[Bob Phillips]

[vba]

Sub LookupPhone()
Dim Surname As String
Dim FirstName As String
Dim iRow As Long

Surname = InputBox("Please supply Surname")
If Surname = "" Then Exit Sub
If Application.CountIf(Columns(1), Surname) = 0 Then
MsgBox "That name does not exist"
Exit Sub
ElseIf Application.CountIf(Columns(1), Surname) = 1 Then
MsgBox Application.VLookup(Surname, Columns("A:E"), 3, False)
Else
FirstName = InputBox("Duplicate Surname, supply Firstname")
If FirstName = "" Then Exit Sub
On Error Resume Next
iRow = Evaluate("Match(1,(A1:A1000=""" & Surname & """)*(B1:B1000=""" & FirstName & """),0)")
On Error GoTo 0
If iRow = 0 Then
MsgBox "That name does not exist"
Exit Sub
Else
MsgBox Application.Index(Columns(3), iRow)
End If
End If

End Sub
[/vba]

[badflyer]

This is great mate, but now i want to return the result of the VBA lookup back to the spreadsheet. i.e

Surname in Cell A1
First name '' A2
Phone '' A3
Email '' A4

By doing this, I can then remove my Vlookup altogether in Excel, and let the VBA take care of things! Cheers!!

[Bob Phillips]

I am confused by your layout.

I thought A1 was the first surname (or heading), A2 was the next etc.

How do you want the code to function, as a UDF or as a breakup macro?

[badflyer]

Sorry to confuse you, Lets start again.

This is what i have at the moment. In column A1 i have surname, A2 First name, A3 Phone, A4 Email. These are currently fixed fields. All my data,in columns are below this.

The user has to enter a Surname in field B1, and this will return a vlookup reply in B3 (Phone) and B4 (Email) that corresponds to the users surname. This is easy enough, however i want a solution that will deal with multiple surnames, for eg, we have about five "Allens" that work here, so in this case i want the option to ask for the persons first name also.

All i want then is to write the lookup into cells B3 and B4 (as above) once that person is found.? I hope this makes sense now?

[Bob Phillips]

It would be much, much easier if you reformat the data to be columnar, not all in the same column (otherwise matching up will be difficult, an d you can get problems with names that are both fIrstname and surname). Is that possible?

Also, UDF or macro?

[mdmackillop]

Here's a listbox solution; Enter the name in A1 and move to A2

[badflyer]

Macro or udf i dont know? I just want it to be easy to use

The data is columnar. I have about 1000 surnames in column A, First names in Column B, phone nums in column C and so on..

All the data starts from row 11 down.

[Bob Phillips]

Well earlier you said,

This is what i have at the moment. In column A1 i have surname, A2 First name, A3 Phone, A4 Email. These are currently fixed fields. All my data,in columns are below this.

[badflyer]

This is a sample of what i have...hopefully it will make sense now.
I enter the surname in B1. Problem is, their are two "Abbotts" and only 1 shows, therefore, i want to show the result based on the user entering the first name as well as surname.

[Bob Phillips]

So you don't need VBA at all!

B3: =INDEX($C$11:$C$18,MATCH(1,($A$11:$A$18=$B$1)*($B$11:$B$18=$B$2),0))

B4: =INDEX($D$11:$D$18,MATCH(1,($A$11:$A$18=$B$1)*($B$11:$B$18=$B$2),0))

which are array formulae, so should be committed with Ctrl-Shift-Enter, not just Enter.

[CCkfm2000]

try this version...

[badflyer]

XLD m8, absolutely brilliant solution! i've never used =INDEX before, so i will now go read up on it. As a furthur enhancement, could we modify this so that the user only needs to type in the initial for the first name?
Also, give a textbox prompt If No matches found? thxs