UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Index(match)+ Data Validation, Office 2007    
 
   
pat2mat2003
post Jan 3 2018, 02:45 AM
Post#1



Posts: 355
Joined: 13-January 06



Hi

In column L1: L50 i have stored contractors names . In column M1:M50 I have stored their Tax Id numbers.
In column A1 i have created a data validation list. When i select a name in a1 B1 should fetch the id od the contractor selected in A1. I have used Index match as under :

= INDEX(M$1:M$50,MATCH(A1,L1:L50))

Is what I am doing the right way?
should I use fixed references in the index match formula or in the validation list

Attached File(s)
Attached File  index_match.zip ( 9.64K )Number of downloads: 3
Attached File  index_match.zip ( 9.64K )Number of downloads: 2
 
Go to the top of the page
 
dflak
post Jan 4 2018, 04:29 PM
Post#2


Utter Access VIP
Posts: 6,230
Joined: 22-June 04
From: North Carolina


The formula you need is =INDEX(=INDEX($L$1$:M$99,MATCH(A1,$L$1:$L$100,0),2)

First note the use of the $ to "lock down" the ranges so they are absolute references and do not change when you copy the formula down.

$L$1:$M$99 - You want to reference the whole of the lookup values.

MATCH(A1,$L$1:$L$100,0) - you forgot the zero.

2 - tell it what column in the range you want to return.

Now I am going to get on my "soap box" and preach the Gospel of Excel Tables. Excel Tables came along with Excel 2007 so you should be able to use them. Several advantages to tables are that they know how big they are so when you you use them to look up things, you don't have to specify extra rows. Tables "know" exactly how many rows they have. No more need for absolute references.

Another nice thing is that tables automatically "extend." If I add data on a row below the table, the table extends to encompass the row and it copies down formulas, validation, etc.

Finally, you can use table headings in formulas. This makes it easier to understand what the formula is doing. Intellisense does an excellent job suggesting table names and table headers for you.

In the attached, I have a working table called Table_Info in columns A:C - I added a column up front called "Item" you can type anything in there you want. When you do, it makes a new line for the table and the validation in Column B is copied down and the formula in column C is copied down.

The lookup formula now looks like: =INDEX(Table_Lookup,MATCH([@Company],Table_Lookup[Company],0),2)

Table Lookup is in Columns M:N. When you add an item to this column, it is available for lookup without changing any formulas that reference this table.

For more information on Excel Tables: http://www.UtterAccess.com/wiki/Tables_in_Excel
Attached File(s)
Attached File  index_match.zip ( 11.18K )Number of downloads: 1
 
Go to the top of the page
 
dflak
post Jan 4 2018, 04:36 PM
Post#3


Utter Access VIP
Posts: 6,230
Joined: 22-June 04
From: North Carolina


P.S. I forgot one thing. I also changed the data validation to lookup from the table: =INDIRECT("Table_Lookup[Company]")

That's one quick way to set up a data validation without using a named dynamic range.

Somehow the validation got "hung up" on the fixed range, so I deleted all my data in the table and started again. One other thing about tables is that even if you delete all the rows, they still "remember" formulas and validation.

Attached is the version with the clean validation.


Attached File(s)
Attached File  index_match.zip ( 11.21K )Number of downloads: 6
 
Go to the top of the page
 
pat2mat2003
post Jan 5 2018, 01:14 AM
Post#4



Posts: 355
Joined: 13-January 06



Hi

Thank you so much. Learned a lot.

PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th October 2018 - 03:00 PM