function to return position in a list

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I'm trying to find a function(or formula) that will return the relative
position of an item selected in a list of items. For example: from a
drop-down list, if you choose the 8th item in the list, the function returns
the value "8"; the 3rd item would return the value "3", and so on.

Any help would be greatly appreciated!
 
See if this example helps:

A1:A10 contains this list:
Alpha
Bravo
Charlie
Delta
Echo
Foxtrot
Golf
Hotel
India
Juliet

B1 contains a Data Validation based on that list.

This formula returns the postition of the
selected B1 item in the source list:
C1: =IF(COUNTIF(A1:A10,B1),MATCH(B1,A1:A10,0),"")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
If you want to use a combo box from the Forms toolbar it will return the
relative number of the selected item.

Or, if the source for your data validation list is a range of cells, say,
X1:X10, and the drop down is in cell A1:

Then you can try something like this:

=IF(A1="","",MATCH(A1,X1:X10,0))
 
Back
Top