Search list with Vlookup

[austenr]

I have been given an assignment to compile a listing of companies we service. I need a way for the user to input the company they are looking for and have it pop up with the following information. For example:

John Brown
Hours 8:00 to 5:00
Backup: Sally Jones


I know that this would be a good time to use ACCESS but I am forced to use EXCEL because our IT department in their infinant wisdom decided we needed Power Point more than ACCESS.

I was thinking about using Vlookup but am open to suggestions. Thanks in advance for your help.

[Jacob Hilderbrand]

VLookUp should work fine for you. Can you post an example workbook showing the layout of your data?

[austenr]

Here is my test file

[mdmackillop]

Hi Austen
You could do this with a userform The attached example shows both a listbox and combobox, although you would use only one. Depending upon your data, a sorting routine may be required to present your companies in logical order, if you with to go down this route.

[austenr]

Thanks MD I like that approach. One more thing I added something to the List box you made. All that I really need now is to have all the fields filled in once you pick a group. I attached your updated file to this reply. Almost there. Thanks a lot.

Also, my VBA is a little rusty, where do I change the name of the button when you open the workbook?

[austenr]

O.K. I got most of it the way I want it. There is a problem though. I deleted some of my groups in column B and they are still showing up in my list box. Can anyone help fix this? If I get that I got what I need. File attached.

[mdmackillop]

To change the button text, right click on the button first, then you can edit the text. The button is added from the Forms toolbar.

This will ignore blank values for your listbox

Private Sub UserForm_Initialize()
Dim Cel As Range
For Each Cel In Intersect(Range("Listing"), ActiveSheet.UsedRange)
If Cel.Text = "Group:" Then
If Cel.Offset(0, 1).Text <> "" Then
ListBox1.AddItem Cel.Offset(0, 1).Text
End If
End If
Next
End Sub

I've attached a sample incorporating DRJ's Bubblesort routine
http://www.vbaexpress.com/kb/getarticle.php?kb_id=103

[austenr]

hey thanks..i figured it out. thanks again .. mark this solved