A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup List to Index



 
 
Thread Tools Display Modes
  #1  
Old January 17th, 2005, 09:43 AM
SSR
external usenet poster
 
Posts: n/a
Default 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  
Old January 17th, 2005, 10:27 AM
R.VENKATARAMAN
external usenet poster
 
Posts: n/a
Default

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  
Old January 17th, 2005, 10:32 AM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 06:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.