work-around for "vlookup: with unsorted data

[mark kubicki]

i have a range of data that i am using for the a drop-down validation list (column 1) (it is entered in an order that makes logical sense for the drop down however, i also want to use that same list for a vlookup table (returning the results from column 2) -it is not "sorted" and would give me problems with the vlookup... is there a "work-around" that would allow me to use the same list for both the drop-down and the lookup without having to create redundant (and potentially unsynchronized) lists?

pushing this out of the territory of an XL function and into VB is O.K.!!!

COLUMN 1
cup COLUMN 2
16
3/4 cup 12
1/2 cup 8
1/3 cup 5.3
1/4 cup 4
tbsp 3
tsp 1
1/2 tsp 0.5
1/4 tsp 0.25
pinch 0.125

 

[Roger Govier]

Hi Mark
Provided you use the 4th argument to VLOOKUP and set it to FALSE or 0, then the table need not sorted
=VLOOKUP(your_Value,$A$1:$B$10,2,0)

--
Regards

Roger Govier


i have a range of data that i am using for the a drop-down validation list (column 1) (it is entered in an order that makes logical sense for the drop down however, i also want to use that same list for a vlookup table (returning the results from column 2) -it is not "sorted" and would give me problems with the vlookup... is there a "work-around" that would allow me to use the same list for both the drop-down and the lookup without having to create redundant (and potentially unsynchronized) lists?

pushing this out of the territory of an XL function and into VB is O.K.!!!

COLUMN 1
cup COLUMN 2
16
3/4 cup 12
1/2 cup 8
1/3 cup 5.3
1/4 cup 4
tbsp 3
tsp 1
1/2 tsp 0.5
1/4 tsp 0.25
pinch 0.125