Excel 2000 vlookup function

[teaeye]

Here is what I am trying to do:

I have a table of names:

A B C
1 Jones Bob 555-1212
2 Johnson Jim 555-1313
3 Smith Kate 555-1414
4 Smith Joe 555-1515
5 Doe John 555-1616

I want to do a lookup in the table which will return all instances of the match. For example, if I want to look up Smith, I want the information on both Kate and Joe Smith returned.

 

[bkpchs237]

teaeye,

The vlookup function will only return one value, the first that matches your criteria, so it will not satisfy your needs. By your statement, you might do well to use the AutoFilter (Data, AutoFilter) and select your criteria using the drop-down arrows.

Hope this helps.

 

[teaeye]

Thank you for your reply. I looked at the Autofilter capability and while it sort of does what I want, it is not exactly what I need to do. I am trying to do a search of the table (it could have 700 - 800 names in it). Is there a macro or another function that allows this type of search?

 

[DaleWatson123321]

teaeye,

An option that will work is to use Excel's "data extraction" capability. This is found under the menu: Data - Filter - Advanced Filter.

Overall, this component of Excel is relatively easy to use - (and is VERY powerful) - but only AFTER one has overcome a couple of annoying BUGS.

One BUG will inform you it's not possible to extract data to a SEPARATE sheet. This bug surfaces when one attempts to use the menu method. However, by using the VBA method, there is NO problem. And of course the VBA method is preferable because it's faster.

The second BUG can surface when one attempts to place "criteria" (for selecting the data) on a SEPARATE sheet. Ironically, it's "preferable" to place the criteria on a SEPARATE sheet because Excel can "become confused" when the criteria is placed on the SAME sheet. Placing the criteria on a SEPARATE sheet makes it FAR easier to work with the criteria.

Because of these BUGS, and the fact that Microsoft and third parties have not provided sufficient documentation on the use of this VERY POWERFUL feature of Excel, it unfortunately has been avoided by many Excel users.

But BELIEVE me... time spent on learning how to harness this powerful feature will be WELL spent !!!

As a "specific" example of its use related to your described task, here's what you could expect...

You could enter the name "Smith" into a cell, and then click a "macro button" that would cause all the records containing "Smith" to be extracted to a SEPARATE sheet.

Please appreciate that such an example is VERY simple and easy to create. However, if required, this same type of process can be used to extract records based on using increasingly "more specific" criteria. For example, one could extract all the records of people based on each person having the following requirements:
a) Last Name of "Smith", and
b) Having an income of greater than $40,000, and
c) Being over the age of 30, and
d) Being Married, and
e) Having 2 or more children, and
f) Whose children are between the age of 6-12 years, and
g) etc, etc, etc.

Also, you can also use "or" in place of "and".

If you'd like, I can email you a zip file of a variety of example files that should help give you a "jump start", and I could also create an example file for the specific situation you described.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[teaeye]

Dale, what you are describing sounds just like what I need. Can you please email me the zip file you mention. My address is tea_eye@hotmail.com.

Thanks again for your help.

Tim

 

[klmorgan]

Interesting note on Advanced filters, I use them a lot and thought the problems were me!

I find that you can get multiple filters on one sheet by recording separate macros to do each filter and then have another macro to run the advanced filter macros. If you copy and change the filter updates in a macro it dosn't seem to like it. First part of each macro should clear the area where the Advanced filter should copy to because if subsequent operations of it finds less rows then the rows that have not been overwritten stay with old data in them

It dosn't like anything above the list that you are using as the base data and I think that it only likes single word field names (labels)

Also I found that once you have a sheet that has Advanced filter problems its best to start again with a new sheet as it really gets confused

I have an entry sheet where I collect the users criteria for the advance filter then use the = to get the info into the criteria ranges. I concatenate dates with >= or <=

I the use = or Dcount to get the info back to the entry sheet.

When they work they seem reliable

Sorry if this rambles a bit!

Regards

Keith