Index/Match to look up a value in one workbook and insert it into.

I need to be able to look up a value in a range on one Excel workbook using a 
code in another workbook.  Can I use Index/Match for this function; looking 
for the value in one workbook and inserting it into another workbook?  
Currently, I use Vlookup, which is cumbersom and awkward and prone to error.

Thank you.
0
Marie (60)
2/16/2005 6:37:07 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
531 Views

Similar Articles

[PageSpeed] 12

You can use =index(match()), but I think you may have as much trouble with that
as you have with =vlookup().

You may want to look at the formula tips that Debra Dalgleish shares:
http://www.contextures.com/xlFunctions02.html
and
http://www.contextures.com/xlFunctions03.html 

Maybe it'll help.

Jean Marie wrote:
> 
> I need to be able to look up a value in a range on one Excel workbook using a
> code in another workbook.  Can I use Index/Match for this function; looking
> for the value in one workbook and inserting it into another workbook?
> Currently, I use Vlookup, which is cumbersom and awkward and prone to error.
> 
> Thank you.

-- 

Dave Peterson
0
ec357201 (5290)
2/17/2005 2:40:07 AM
Thank you Dave,  

This was very helpful.

... .. .. .. .. .. ..  Kathy

"Dave Peterson" wrote:

> You can use =index(match()), but I think you may have as much trouble with that
> as you have with =vlookup().
> 
> You may want to look at the formula tips that Debra Dalgleish shares:
> http://www.contextures.com/xlFunctions02.html
> and
> http://www.contextures.com/xlFunctions03.html 
> 
> Maybe it'll help.
> 
> Jean Marie wrote:
> > 
> > I need to be able to look up a value in a range on one Excel workbook using a
> > code in another workbook.  Can I use Index/Match for this function; looking
> > for the value in one workbook and inserting it into another workbook?
> > Currently, I use Vlookup, which is cumbersom and awkward and prone to error.
> > 
> > Thank you.
> 
> -- 
> 
> Dave Peterson
> 
0
JeanMarie (6)
2/22/2005 1:53:02 PM
Reply:

Similar Artilces:

More than one cell with label
I am plagued with an Identify Label popup when when pasting large MS Word documents into Excel. The message is "There is more than one cell with this label". In the text box there is the letter "a". The instruction is "Select the cell to contain the label to use". Whatever cell I enter, e.g. "A1", I get a popup with the message "That cell does not contain a. Use it anyway?" If I click Yes or No or Cancel, the Identify Label repeats. I have to use Task Manager to close Excel. Tools : Options : Calculation : Accept labels is off. The MS Word do...

How do capture the first three characters from one cell to another cell #2
Perfect. Thanks -- ExcelAle ----------------------------------------------------------------------- ExcelAlex's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1555 View this thread: http://www.excelforum.com/showthread.php?threadid=27129 ...

constant values
I have a very simple question, which I am not able to resolve though. I have created a query, which combines records from two tables. Works nicely. Now I would need to add several columns with either a) fix values, which will never change b) values which apply for all records, but which I would like to enter within a form every time I would like to run the query. The background is that I will use access as a mapping tool, so an original file will be uploaded and result in a table with the same name always. The query now takes the mappings for another table and combines the ...

Can't insert PowerPoint slides into Word
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Seems VBA macros isn't the only feature that's gone missing in the upgrade to 2008. Now it is no longer possible to insert PowerPoint slides into a Word document (which I have been doing with every version of Office since there's been one). If you drag a slide from Slide Sorter, or copy and paste, you just get an error message that there isn't enough RAM (apparently 4 GB isn't enough!). The only "solution" tech support has been able to give me is to export the PowerPoint presentation to pict...

Trouble matching overdue payments
This seems to be a constant problem that I would like to find a fix for. Money (2005 s.b.) somehow doesn't match payments from my credit card to reoccurring bills. It then shows that I have an overdue bill even though I paid it. Is there some way to match the overdue bill to a payment I already made? It seems like my only options are "skip occurrence" (which I always do) or "enter into register". Both options suck. Anybody have any ideas? Thanks. ...

when copying a worksheet to a new one, some of the information do.
I had a long spreadsheet that I wanted to break in 2. I created a new worksheet and copied the rows and columns that I needed to copy. When I was done, column "A" was not copied and the rest of the columns were misalligned. Any suggestions please ? What I would do is make two identical worksheets and delete in each one what's not needed. If you click on the empty gray block about Row 1 and to the left of Col. A, it's selects the entire worksheet including column sizes. Copy and paste into a new worksheet, and then delete in each one what you don't want in each o...

To index or not to index
I have a table of stuff stored in a repository and an attached table of inventory dates, linked one-to-many by an Autonumber ID field. I regularly need to find the oldest or newest inventory dates (or all, in order by date) for each item record in the inventory table, which is normally an automatic case for indexing. But this stuff is not inventoried very often, so far, only two out of over 80,000 records have three records in the inventory table, all others have zero, one or two inventory records. This is NOT going to change. It will likely be decades before there are as many as...

How can one remove a solid line that appears under a paragraph?
There is a solid horizontal line that somehow appeared at the bottom of a page of MS Word text immediately below the last line of text. Trying to delete it by selecting and then backspacing does not do the job. Erasing text immediately above it causes the line to move up one text line. Trying to remove it by using the "underline" icon also fails. It is very importan thtat this line be removed. Any hints will be appreciated. Hi, For an explanation of how such a line is created and instructions for removing it, see the section entitled "Lines of Various Shape a...

putting a value in a field(correct spelling of this posting this t
Hello: With the use of VBA, what code would you use to populate the Employee ID field in one window with the same empliyee ID (value) from another open window? Thanks! childofthe1980s Once you have added both windows to VBA and added the Employee ID fields the code needed would be something like this. Sub EmployeeID_AfterUserChanged() Window2.EmployeeID = EmployeeID End Sub If they are in separate Great Plains Dictionaries you will need to create a reference to the other dictionary (shown as projects in VBA) and then include the Project in the qualification. Project2.Window2...

Workbook with macros hangs up when opening, only with certain user
Chip Pearson has some notes about startup problems at: http://www.cpearson.com/excel/StartupErrors.htm Ron516 wrote: -- Dave Peterson ec35720@msn.com ...

INDEX/MATCH help
I just learned about the INDEX/MATCH function while searching some of the Excel tip pages - and I think I can make good use of it. What I want to do is this: worksheet 1 - is the format of our Income Statement worksheet 2 (titled TB) is our Trial Balance. I want worksheet 1 to read TB (worksheet 2) and pick up the YTD amount for each account #. I tested this out and my formula is working fine. However, sometimes an account # on worksheet 1 doesn't appear on Worksheet 2 because there wasn't any activity. In this case I get a $N/A. I need to edit my formula so that if there ...

How do I make a chart with bar graphs on one axis, and a line on a secondary axis?
Hi all. I have some data that I need to plot (Excel v.X running on Mac OS Panther). I've currently got two charts: one is a bar graph with two groups, and each bar has two colors (one for each of the groups) - 100% stacked column. I have another graph which is a very simple line graph. Both graphs have the same X axis (with 7 data points), but different Y axes. How do I combine them into the same chart? I know how to make a secondary axis, and the example in Excel (a "custom" chart) has a bar graph plus a line graph on the secondary Y axis, but its bar graph is a simple bar graph...

Create Clustered index or Covering Index
Which would be better on a 3 column temporary table? A Clustered index or a Covering Index? In this case the uniqueidentifier is always the same and is done this way because there are about 20 procedures that already use this as a static table and I don't want to impace all the procedures with this change. CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE CLUSTERED INDEX idx on #temp (CID) or CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE INDEX idx on #temp (CID) INCLUDE (PID,a) Thanks, Tom Tom, Better for what? It depends....

Inserting the file path in a cell
I am using Excel 2003. I know how to insert the full path and file name in a footer, however, is there any way (possibly a function) to display the full path and file name in a cell? Yes. Use the =CELL("filename") function Have a good day -- Gary''s Student "netorius77" wrote: > I am using Excel 2003. I know how to insert the full path and file name in a > footer, however, is there any way (possibly a function) to display the full > path and file name in a cell? Too smart by far - thanks exactly what I wanted. "Gary''s Student"...

Inserting TIFFs into Word X
After many months of successful inserts, now when I Insert a Picture = from=20 file as a TIFF file, the picture box appears without a visible graphic. = Other=20 file formats work fine. Ideas? Might you have "show image placeholders" checked in Prefs under View? DM "Jo Anne" wrote: > After many months of successful inserts, now when I Insert a Picture from > file as a TIFF file, the picture box appears without a visible graphic. Other > file formats work fine. Ideas? Sorry, reread your post, unlikely to make a difference if other formats work. Post back with...

VLookup not returning the last value
Hello, I am using VLOOKUP =VLOOKUP(D7,X4:X7:Y4:Y7,2,FALSE) My problem is that X7 is not showing in the dropdown list. The dropdown is simple text a value just like the other rows. Can anyone help me understand why I don't see this value? Thanks very much! >>VLOOKUP(D7,X4:X7:Y4:Y7,2,FALSE) try VLOOKUP(D7,X4:Y7,2,FALSE) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "George" <George@discussions.microsoft.com> wrote in message news:07C02EA4-F498-4A0D-B161-5DD5411520C9@microsoft.com... > Hello, > I am using VLOOKUP =VLOOKUP(D7,X...

Table sorting and v look up possible &
Hello, Just need some advice with regards to the below. I have 2 tables. The first consists of 1 column of names and several other columns of other details. This table contains the records of people i need to update with details of the 2nd table.... The 2nd table contains 2 columns of the same names but split, with first name being in a separate column than the surname. Then further columns that have more info in. This table has 11000 peoples names in as this data is a simple data dump, opposed to the exact names of the people in table 1 i need. I will use a v look up but after i ha...

Excel slow to load blank workbook on startup
Hi Excel 2003 Excel opens quickly to display the program frame but with the grey background instead of the cells of the initial worksheet. About 5-10 seconds later it loads the worksheet. It might be loading it from our companies slow server. What is the standard workbook that it's trying to load? What is it called? Is there path to it in Options? If there is I can't see it :-( Hope you can help me. Cheers Dave F. Excel isn't like MSWord. There are no standard workbooks that have to be opened when excel opens. Each user does have the option of adding any workbooks he or ...

Indexes in tables.
Hi, Can I add index for tables in SQL Manager. For example for SOP30200 index for any field. Will it be a problem with program or not? Any other ways? Thanks, Vitali I believe any future upgrade will blow away your index. -- Charles Allen, MVP "Vitali V" wrote: > Hi, > > Can I add index for tables in SQL Manager. For example for SOP30200 index > for any field. Will it be a problem with program or not? > Any other ways? > > Thanks, > > Vitali > > > Suggest you leave the tables alone. You'll just get into a pickle.. "Char...

External web query
I am not a programmer and a new excel user. I have an external web query that I am using tracking external links from a web site. I need to create an additional column with the web page info that relates to where the link is coming from. I tried but when I refresh the data it inserts cells with the any new links listed but then that throws off my additional column info. I looked in Data Range Properties and checked the middle option but that doesn't work the way I thought it would. Suggestions? -- Message posted via http://www.officekb.com ...

Match & Index??
I have the following info in different workbooks. In workbook 1, I have in Columns A,B,C,D: Mark & No. Start End Deal SLGG1234 3/15/2004 6/15/2004 211 SLGG1234 1/1/2004 3/14/2004 111 SLGG1234 6/16/2004 8/15/2004 311 SLGG1255 2/13/2004 8/15/2004 411 In workbook 2, I have Columns A,B, C: Mark & No. Date Deal SLGG1234 3/14/2004 SLGG1234 6/14/2004 In Column C of workbook 2, I want a formula to look at Cols A and B. Compare the info in them to Cols A,B and C in workboo...

Looking for an envelope template in pub w/return address on back
I would like to add my return address on the back of the envelope in Publisher. Does anyone have a template? ...

text and picture colour matching
Hi there I am producing a document which has a picture and text. I am trying to make them the same colour. I set the text and the picture to the same rgb colour numbers 255 (i.e. royal blue). They look the same on the screen and yet when they print out the picture and the text are very different shades of blue. It's an issue I've had before in pub 2003 but didn't in pub 2000 on the same printer! Any suggestiions greatfully received. Thanks Ken Are you running the most current printer driver? -- JoAnn Paules MVP Microsoft [Publisher] "Ken" <anonymous@discussio...

Look up combos in Query Criteria?
I am trying to build a database that narrows the record choices as different fields are selected. I believe that this would be a query, but whenever I build a query and select a field choice, the record in the main table changes. For example: Car Dealer enters car ID, make, model, and color into main database. I as a consumer, select make model and color, and the query lists only records that match. I would prefer a lookup box in the query, as we don’t want to rely on stuff being named correctly, or maybe a burgundy from the list, would suffice for a red preference. Keep in mind, ch...

subtotal, match?
Morning all. I need to do a name check to match with a subtotal function. Is that possible? I was thinking that subtotal(Func_Num,match(....)) might handle it, but I don't see anything that'd allow for that in the list of function numbers for subtotal. My goal is to perform a subtotal type operation, to compare names in a field range from one worksheet to another. I'm already performing a subtotal operation on one field, and wanted to do an if test on the names that show up with each operation. With the false response, I'll then be changing the name eleme...