=VLOOKUP Question (Or use something else?)

[Djchaney3]

I have a workbook with 5 Worksheets included in it. I have a ListSource worksheet that has the counties of Ohio listed in row D and the sales tax for the counties in row E.

On the sheet I am working with I have a drop down box in the county column directed to Column D in the ListSource worksheet to populate the Counties for my customers. I would like to have column Q automatically populate with the correct county Sales tax when the count is chosen.

What is the easiest way to accomplish this task?

[SamuelT]

Hi,

As the Counties aren't in the left-most column, it's probably best to use INDEX/MATCH. Presuming your complete range of data in ListSource is A1:Z100 and your data starts on D2, put this into Q2:

=INDEX(ListSource!$A$1:$Z$100,MATCH(D2,ListSource!$E$1:$E$100,FALSE),5)

HTH,

SamuelT

[Djchaney3]

With this there is no direct link to the drop down box, so when I chose the county I get nothing in return.

Let me see if I can explain myself better... (I moved the data on the ListSource Sheet)

Working on:
Worksheet: Price Prot.No Metter
Column E = County (Select County with Drop Down List)
Column Q = Sales Tax Rate (Attempting to auto fill when County is selected in Column E)

Data for Counties Drop Down List and Sales Tax rate is located on:
Worksheet: ListSource
Column A: County (Data Starts on A2 - Moved the data over)
Column B: Sales Tax Rate

I would like to have Column Q on Worksheet Price Prot.No Meter Auto fill when Column E is Selected from the Drop Down List.

[SamuelT]

Hi,

=INDEX(ListSource!$A$1:$Z$100,MATCH(D2,ListSource! $E$1:$E$100,FALSE),5)

The highlighted cell reference is the cell with the County drop-down. Therefore, with the changed data, put this in Q2:

=INDEX(ListSource!$A$1:$B$100,MATCH(E2,ListSource!$A$1:$A$100,FALSE),2)

As your data is ListSource is now in Column A, you could also use VLOOKUP:

=VLOOKUP(E1,Listsource!$A$1:$B$100,2,FALSE)

Let me know if that works.

SamuelT

[Djchaney3]

Killer it worked great thanks alot...