If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Runtime Error 381 Couldn't set list property, invalid array index | Newbillian | General Discussion | 2 | June 17th, 2004 11:44 AM |
Lookup based on list of names | tojo107 | General Discussion | 1 | June 15th, 2004 07:30 PM |