Lookup List to Index
 

Hello,

I have a sheet with 2 columns as Below
A B
1 Apples 1
2 Oranges 3
3 Bannanas 2

Cells in Column A have data validation with values say
Apples
Bannanas
Oranges

What I want in Cells of Column B is the Index i,e the Number 1 or 2 or 3 as
and when "Apples" or "Bannanas" or "Oranges" are selected


is this possible without using any macro but with Worksheet formulas?

Regards,
SSR

create a named range somewhere e..g <fruits
the range will be

apple 1
orange 2
bananas 3

in the columb B of your data type
=VLOOKUP(A15,fruits,2,FALSE)
as you enter in column A by validation list automatically the appropriate
index will be entered in column B.
==============================



SSR wrote in message
...
Hello,

I have a sheet with 2 columns as Below
A B
1 Apples 1
2 Oranges 3
3 Bannanas 2

Cells in Column A have data validation with values say
Apples
Bannanas
Oranges

What I want in Cells of Column B is the Index i,e the Number 1 or 2 or 3
as
and when "Apples" or "Bannanas" or "Oranges" are selected


is this possible without using any macro but with Worksheet formulas?

Regards,
SSR

It is if you put the items in a table elsewhere on the sheet, say H1:H10,
and use that range in your DV list. Then in B1 use

=MATCH(A1,H1:H10,0)

--
HTH

Bob Phillips

"SSR" wrote in message
...
Hello,

I have a sheet with 2 columns as Below
A B
1 Apples 1
2 Oranges 3
3 Bannanas 2

Cells in Column A have data validation with values say
Apples
Bannanas
Oranges

What I want in Cells of Column B is the Index i,e the Number 1 or 2 or 3
as
and when "Apples" or "Bannanas" or "Oranges" are selected


is this possible without using any macro but with Worksheet formulas?

Regards,
SSR