Index Match Functions #3

I am building a spreadsheet query tool, the data is exported via flat
file and I convert it to .xls. There are mulitple files in the work
book and I need to be able to query the data from multiple
spreadsheets.  Would index / match be the best functions? Is there
some good examples of how to set them up? I am fine with lookup
functions but this is too complex for that and I am not familiar with
the index and match functions.

0
9/21/2007 4:36:52 PM
excel 39880 articles. 2 followers. Follow

1 Replies
208 Views

Similar Articles

[PageSpeed] 48

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html  (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html  (for =index(match()))

randi.smith@comcast.net wrote:
> 
> I am building a spreadsheet query tool, the data is exported via flat
> file and I convert it to .xls. There are mulitple files in the work
> book and I need to be able to query the data from multiple
> spreadsheets.  Would index / match be the best functions? Is there
> some good examples of how to set them up? I am fine with lookup
> functions but this is too complex for that and I am not familiar with
> the index and match functions.

-- 

Dave Peterson
0
petersod (12004)
9/21/2007 5:13:33 PM
Reply:

Similar Artilces:

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. ...

Insert Picture #3
I have never used excel before. I need to make a form with a box. When you click on the box it should ask if you want to add a picture to the box. It should also format the picture so that it is the same size as the box. Can this be done in excel or do I have to use another application for this? I really need to find the answer to my question so please post a response if you have any idea of how this can be done. What type of form? A userform or? What will you do with the picture when it is inserted? What you are wanting to do and why is unclear. Gord Dibben MS Excel MVP On Thu, 2...

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...

Function in query or recordsets and arrays?
Hi I have to use the results of a query as the basis for a number of calculations to produce a summary table. I can do this by feeding the data into variables from an array based on the recordset (or directly) and then use append to place the results into the output table. I was wondering if I should perhaps make a function that is called from within a query instead but having never done this I'm not sure. Purpose: Calculate weekly wage information from Jobdata Process as follows: Grouped select query returns all records for specific week ordered by employee with sum of hours for ...

problem #3
Hi I have a problem in excel when i try to import data from MSSql with Import external data: I must use the same condition in two places in the same query..something like that: select col2 from d where col1=? union select col3 from d where id not in (select col2 from d where col1=?) this query works perfectly in MSSql, but when I try to do this in my ".dqy" file the excel tell me I have an error in my sql... pls help me ...

CRM 3.0 and Calendar Items
Hello all, I'm wondering if there is a way to use workflow (or any third party tool) to create calendar items (instead of Tasks) automatically in CRM 3.0. I understand that calendar items are more complex than Tasks and therefore harder to automate, but if I could find a way to do this, one of my partner's prospects would sign on for CRM tomorrow. Thanks for any help, Andrew Hi Andrew Appointment type activities automatically shows up within the CRM calendar. Is there something else you are looking for here? Kind Regards Eddie Fourie MBS Specialist (Available for outsource...

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 ...

Conditional Formatting. Can i get more then 3?
Is there any way to get more then 3 conditional formats for a group of cells? I would like to highlight different names in a list of work assignments so i know quickly who has what. I need more then 3 colors though. Is there a way to do this? thanks Matthew, here is one way with a worksheet change macro Private Sub Worksheet_Change(ByVal Target As Range) For Each c In Worksheets("Sheet1").Range("A2:A12") If c.Value = "a" Then c.Interior.ColorIndex = 1 If c.Value = "b" Then c.Interior.ColorIndex = 13 If c.Value = "c" Then c.Interior.C...

IF and LOOKUP functions
I have one spreadsheets with two tabs - one is called full and one is called partial. What I would like to do is is pull information from full sheet to partial, i.e. if in the full sheet there are six lines with the number 1234 in cell A and 6 lines of numbers in cell B, I would like to get them over to cell B on one line in the partial sheet that has 1234 in cell A. I hope I've made sense. thanks much! JBennett >.. there are six lines with the number 1234 in cell A .. Do you mean six rows with the number 1234 in column A? >.. would like to get them over to cell B on one...

Find & Replace #3
I need to find empty cells and replace with 0. I thought I used to use "" to determine an empty cell or maybe I've just gone brain dead. Thanks Hi Nolan! Select the range Edit > GoTo > Special Check Blanks OK Type 0 then use Ctrl + Enter But this will only cover cells that are blanks and will not affect cells where formulas resolve to "". -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. Did you really type in the double quote...

Paste option does not appear to be functioning correctly?
I have two different versions of Office 2007 that are installed on different laptops. In the last week or so, I have noticed (as has one of my co-workers!) alot of problems with getting the Cut/Paste or Copy/Paste to work. The text will be cut from the documents but nothing pastes in and I have to undo. Also, when trying to open .docx files, Office keeps going into setup. I uninstalled, reinstalled and updated both versions to no avail. I don't know if this came over in a prior update. I am wasting so much time re-typing information I am never going to meet my deadline...any ...

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....

send to #3
Hi I was using OE and changed to Outlook 2003. When I right click on my screen and select new, there are numerous shortcuts that appear one of them being outlook express mail message. How do I get it to use outlook send instead? Also when clicking on I file send to mail recepient it sends it through OE and says it is not my default mail... can how do I get it to use outlook instead. In tools internet options programs email microsoft office outlook is default. Thanks again ...

CRM 3.0 via web browser
Hello I have installed CRM server3.0 on SBS 2003 + sp1 and it is working properly. SBS is installed in the environment of WMWARE and my company has also AD "Falkon" with windows server 2003. I want to use CRM from web browser after logging on the AD "Falkon" i can use and make changes in sharepoint portal by using 162.9.134.132:8081 and with 162.9.134.132 i can see welcome page from SBS 2003 but when i try to connect CRM via web browser using IP 162.9.134.132:5555 it ask me username and passowrd and after accpeting username and password but Internet Explorer closed ...

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...

Regarding Table_Compare function
Hey The below code doesn't give expected result. l_Return = Table_Compare(table tablecmp); if l_Return <> STATUS_SUCCESS then fResult=Table_SetCreateMode(true) ; fResult=Table_DisableErrorChecks(false); {temp storage of data and delete table} release table tableComp ; close table tableComp ; open table tableComp ; close table tableComp ; {restore data and delete temp table} fResult=Table_SetCreateMode(false) ; fResult=Table_DisableErrorChecks(true); end if; The above Table_Compare function doesn't capture if any index/key changes....

Increment A2 from A1 and A2 Sum #3
I have two cells: A1 - manual value B1 - automatic value (formula) = B1 + A1 (error: reference circular) Note: I have this formula repeated in some lines: = B2 + A2 = B3 + A3 .. -- Message posted from http://www.ExcelForum.com i'm not sure i understand what you're asking - the "circular reference you're getting is because you're trying to do a calculation in B1 whic is asking for part of it's answer in B1... am i helping at all -- Message posted from http://www.ExcelForum.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...

Column Chart #3
Hi All, Can someone please tell me how to put the label on the bar ? I can show only the value or only the label but would like to show both. I am sure the gurus over here can help. I want to show the label on the bar itself(vertical) on top of the bar in different color. see e.g (Thats the best I can do :-) |t| |t| |i| |i| |t| |t| |l| |l| |e| |e| |1| |2| Thanks in advance Andy Bonsour� I'm not � guru... so much tricks I found there ... I suppose guru sometimes are tired ??? ;o))) my solution : http://cjoint.com/?loaOUzacaC regards Andy wrote: > Hi All, > Can...

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...

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...

Functions newsgroup
What happened to the Functions newsgroup? Its gone. Is this the start of the demise of the Microsoft newsgroups? Where would I go to find the forums they talked about in a recent posting? Thanks for your time. Otto http://social.msdn.microsoft.com/Forums/en-US/categories see also http://www.rondebruin.nl/nntpbridge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Otto Moehrbach" <moehrbachoextra@bellsouth.net> wrote in message news:eKFSeCaBLHA.5584@TK2MSFTNGP06.phx.gbl... > What happened to the Functions newsgroup? Its gone. ...

Excell XP
We are working in Excell XP. The HOME key works as follows: HOME (alone) puts the cursor in cell A1 CTRL+HOME puts the cursor in the first cell of the current row These keys are working just the opposite as they did in a previous version. I was wondering if there is anyway to change the way the HOME key functions? Thank you. Abby "Transition Navigation Keys" may have been turned on under Tools, Options, Transition. -- Jim Rech Excel MVP Jim- The "Transition Navigation Keys" are already turned ON. But when I turn them OFF, we get the keys to operate the wa...

Phone Number Formats #3
Hello Everyone, I have read a lot of threads about the phone number format but did not find anything that would solve my problem. Like a lot of you, we have customers/vendors from around the world and I would like to customise the Customer/Vendor phone numbers to display in the correct country format. UK : 1234-5678 France : 12-34-56-78-90 CAN/US : (123) 456-7890 I need to modify the format according to the country of origin. I would like this modification to be in Modifier/VBA if possible. Any tips or tricks to achieve this is welcome -- Sebastien Picard ML3 Business Solutions Soun...

Time Function
=IF(II22<TIME(7,30,),I22-TIME(7,30,),"") Hi there, in I22 the time is 07:38. I would expect the above function to return a blank cell. Instead it returns 00:08. Why? many thanks, Danny I tried various stuff, 7.38, 7:38 as text, and couldn't get anything other than blank. -- HTH Bob Phillips "DannyJ" <youhavegottobekidding@nospam.com> wrote in message news:ujNy75xiFHA.3336@tk2msftngp13.phx.gbl... > =IF(II22<TIME(7,30,),I22-TIME(7,30,),"") > > Hi there, > > in I22 the time is 07:38. I would expect the above function to ...