Hi Carlo,

Thanks for this. It works. Extending on this idea I have another list
whereby if the selection in column A is Prime Mover then options should be
50, 70, 110, 130, or "by Supplier"
For the rigid, the options should be 10,20,45 or 50. For tanker the only
option should be Not applicable. So the overall list should be
10
20
45
50
70
110
130
By Supplier
Not applicable

From your previous example, everything worked well but the ranges all began
from the top row. Here the ranges are different. ie Semi should only address
50 and upwards but Rigid only up to 50. I'm able to get it to work but under
Rigid the choices are 10,20,45,50 which is fine but the Semi also starts from
10 when it should be 50 all the way to the end of the list excepting Not
Applicable which only applies to Tanker.

Any ideas much appreciated.
"carlo" wrote:

On Nov 13, 9:21 am, markmcd wrote:
How do I get a selection via a vlookup formula? I have several columns of
data with data validation. In the first column is the choice Rigid, Prime
Mover or Tanker. In another column, the choices are for Rigid choice 1 and 2,
for Prime Mover the choices need to be 1, 2, 3, 4 and for Tanker the only
choice is to be Not Applicable. If I select Rigid, I don't want to see the
options for Prime Mover or Tanker. I have created a table of data like so:

Prime Mover 1
Prime Mover 2
Prime Mover 3
Prime Mover 4
Rigid 1
Rigid 2
Tanker Not applicable

The numbers are only for ease of demonstration, the data is actually text.
Is it possible to look up Prime Mover (via vlookup) to then determine which
choices should be made available. I have read Debra Dagleish's document on
data validation but it doesn't state this situation very clearly. Any help
appreciated.

I have a solution for you, but there are maybe better solutions out
there.

you have to spare 3 columns, let's say colums "IT" "IU" and "IV"
In column A you have your choices "Prime Mover", "Rigid" and "Tanker"
in Column B you want to select according to your choices in A
In Column "IT" you put following formula: =IF(A1="",6,IF(A1="Tanker",
5,1))
In Column "IU" following: =IF(A1="Prime Mover",2,IF(A1="Rigid",
4,IF(A1="Tanker",5,6)))
copy those formulas down.
Now in column "IV" you enter those values:
IV1 = 1
IV2 = 2
IV3 = 3
IV4 = 4
IV5 = not applicable

now the data validation formula in Column "B" should look like this:
=INDIRECT("IV" & IT1 & ":IV" & IU1)

Hope that is clear, but as I told you before, i believe that there are
better solutions
out there. But It works, and maybe you can use it.

Carlo