Multiple Vlookups

Posted by g on July 23, 2001 2:07 PM

Is there any way to Lookup an item when there is more than one in a column?? I want to see the individual items down the page but excel keeps repeating the same one over and over when I use Vlookup. example:

TYPE DEAL

healthcare 1
healthcare 2
Technology 1
Finance 1
Healthcare 3

If I wanted to do a Lookup and list all of the healthcare deals down the page, how would I do this?? Vlookup keeps repeating deal #1

Posted by sean tobin on July 23, 2001 2:10 PM

What is the formula you are using?

Posted by g on July 23, 2001 2:15 PM

=vlookup($a$1,"range",2,false)

if A1 contains healthcare, how can I get the individual deals listed down the page??

Posted by Sean Tobin on July 23, 2001 3:51 PM

I think your best bet is to Filter the data using Advanced Filter and using a criteria range. This can be done by inserting buttons, i.e., "Filter" and "Unfilter" and assigning macros that filter the data based on the criteria.

Ex.

Your table range is A3:B8
A3:Type B3:Deal
A4:Healthcare B4:1
A5:Healthcare B5:2
A6:Technology B6:1
A7:Finance B7:1
A8:Healthcare B8:3

Above that is your criteria range
A1:Type
A2:Healthcare
I have created two buttons next this data, one called "Filter" and one called "Unfilter" I have assigned a macro to "Filter" that filters the data based on the criteria.

Sub Filter_Type()
Range("A3:B8").Select
Range("A3:B8").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("A1:A2"), Unique:=False
End Sub

Then I created another filter that will reset the data back to normal:

Sub Unfilter_type()
Range("A3:B6").Select
ActiveSheet.ShowAllData
End Sub

This may be a bit confusing but if you leave me your email address I can send you my example.

Sean

Posted by g on July 24, 2001 5:32 AM

Sean, it made perfect sense. thanx for your help.