Explain INDEX MATCH

Can someone please explain the Index, Match formula to me?  It is used in a 
spreadsheet and I need to understand it.  Thank.
0
Utf
4/6/2010 5:26:08 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
940 Views

Similar Articles

[PageSpeed] 33

Nadine wrote:
> Can someone please explain the Index, Match formula to me?  It is used in a 
> spreadsheet and I need to understand it.  Thank.


http://www.contextures.com/xlFunctions03.html
0
Glenn
4/6/2010 5:35:20 PM
There are many different potential ways to write a formula using Index and 
Match.

If you post the specific formula, it may help someone explain the usage 
further.

Very briefly the syntax for Index is (Array,Row num, [Col num])
Thus Index(A1:A5,3) would return the value in A3 (the third postion in the 
array)

If data cannot be sorted to allow a Lookup,Vlookup or Hloolup you may use a 
Match function to get a position
        A            B
1  Apples         7 
2  Pears          5
3  Grapes        3
4  Oranges      4
5  Pinapples     2

The function Index(B1:B5,Match("Pears",A1:A5,0)) would retun the value 5

Match ("Pears",A1:A5,0) finds the position of the first item in A1:A5 that 
exactly equals (signified by the ,0) "Pears"  in this case 2

The function is then reduced to Index(B1:B5,2) which returns the second item 
in the array B1:B5 which is the number 5
-- 
If this helps, please remember to click yes.


"Nadine" wrote:

> Can someone please explain the Index, Match formula to me?  It is used in a 
> spreadsheet and I need to understand it.  Thank.
0
Utf
4/6/2010 5:52:40 PM
Hi Nadine,
Take a look to Debra web, she has an excellent explanation and examples

http://www.contextures.com/xlFunctions03.html

"Nadine" wrote:

> Can someone please explain the Index, Match formula to me?  It is used in a 
> spreadsheet and I need to understand it.  Thank.
0
Utf
4/6/2010 5:57:04 PM
Reply:

Similar Artilces:

using a name label within an index function
I have defined a name label which contains a path to an external lookup workbook. If I put the path in each formula in each cell the formula looks like this: INDEX('c:\directory to workbook\workbook.xls'!MYDEFINEDRANGE;10, 12) I want to use it like this: INDEX(NameLabelThatContainsThePathToWorkbook!MYDEFINEDRANGE;10, 12) But this doesn't work. I'm using Excel 2002. Please help me. Thank you... Hi not possible with Excel's build in functions. Have a look at: http://tinyurl.com/2c62u -- Regards Frank Kabel Frankfurt, Germany "Floran Stuyt" <nospam@nospa...

MAtch Question
Is there a way to match a word in a cell with other words? Example A1 = Ford Probe, B1 = Ford Escort, C1 = VW Bug A4 match the word probe. Hi not really sure what you're trying to do. What is your expected result in cell A4? -- Regards Frank Kabel Frankfurt, Germany "Lee" <allexandi9@mchsi.com> schrieb im Newsbeitrag news:36C1399C-5EB8-4135-AB94-5F06A0719702@microsoft.com... > Is there a way to match a word in a cell with other words? Example A1 = Ford Probe, B1 = Ford Escort, C1 = VW Bug A4 match the word probe. What i would like to see is Ford Probe in cell A4. ...

matching problem...I think
Hi, I have a worksheet with 3 columns (A, B & C) of numbers. Somehow, I need column "D" to get the first zero in column "A" to match up with the first number that appears in column "C", the second zero in column "A" to match up with the second number that appears in column "C", the third zero in column "A" to match up with the third number that appears in column "C", etc.... Please advise, Steve 199 169 276 243 413 390 209 179 210 180 288 255 414 391 167 33 30 245 400 167 0 245 167 0 392 245 283 250 204 174 275 ...

RE: Matching names and inserting spaces
Thank You! This was very helpful! I have a new question that goes along with this. Is there a way to expand on the cell selection? Besides the names in Column B, I also have data in Columns C, D, E, & F that I need to keep with the names in Column B. Thanks Again! "Xt" wrote: > On Feb 13, 10:28 am, ILunacy <ILun...@discussions.microsoft.com> > wrote: > > I hope someone can help me out with this. Say I have column A filled with > > like 500 names, and column B filled with 300 of the same names. Is there a > > way or a formula to match ...

select columns by column index
Hi to all. I am having a small problem. I would like to select columns by their indexes. psedou script is as follows. select column(1), column(2) from table Is it possible to do ? and (Number) is going to be dynamic. Thanks in advance Best regards, Ozgur Tayfun __________ Information from ESET NOD32 Antivirus, version of virus signature database 4611 (20091116) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com It's usually considered good practice to avoid depending on the order of columns in a table. So I would reco...

Money not matching tranactions correctly
I'm using Money 2006 Small Business V 15.0.150.513 I've used Money for many years, but refuse to upgrade again because MS just keeps adding more and more 'flashy' features whilst some of the most 'useful' basic features don't work properly. Here's another one that's caused me problems in every version I've used: After importing my bank statement QIF file, Money tries to match any transactions it thinks could be duplicates. Great idea and a real bonus if, like me, you import statements for two or more accounts with transfers between them. However...

Unable to control indexing in Outlook 2007
I am unable to choose which Outlook 2007 data files will be indexed after reinstalling Office (after a clean install of Windows 7-64 bit). Only my main file is being indexed. When I go to indexing options the box "index messages in these data files" is empty (does not even show my default ..pst) and I am unable to type in it to add any of my personal .pst files. So when I perform a mail search, it only looks in my inbox and sent items. Those do seem to be fully indexed, however. -- Silvia ...

VBA: How to match two sets?
In VBA for Excel (2007), is there an easy way to get a True result if *any* character in one set matches *any* characters in another set? If I understand the Like operator, the result is True only if *all* characters in the first set match a character in the second set. I would like to pass a string of debug parameters to a UDF. Each parameter is a single character, such as: "M" = Display a message "B" = Set a breakpoint "I" = Dump some info to the immediate window. I would like to pass these as a string that can include zero or more ...

How to create end index tabs for .PDF document
I am attempting to create an electronic book with several reports that need to be separate by labeled index tabs before converting to the .pdf format. Is there a way to do so where the tabs will extend beyond the length of the pages or a software program that will allow me to create and insert end tabs? ...

Match
I have created a Validation List called Suppliers, and then once I have picked a supplier I want it to pull over the correct Account Code. An example of the list I have is: A1 B1 1 Fred Bloggs C0011 2 Snoopy C0022 3 Donald Duck C0033 4 Mickey Mouse C0044 5 Pluto C0055 6 Danger Mouse C0066 So when I pick from the drop down list (which is on another sheet) Fred Bloggs, I want it to pull over the code C0011 in a separate cell. Not sure if I have made this clear or not, but any help will be appreciated. I trie...

Index and match formulas
I am trying to use the index and match function to pull data from one table into another. If I had the table below (which is A1:F7) does anyone know a formula that could look at coloumn "F" find all rows that have the "30" and pull info from column "B" into a specified cell (J1). When I use =index(a1:f7,match("30",f1:f7,0),1) only price (B2) is correctly pulled into J1. if I want to pull information into J1:J7 a get a repeat of price (B2) and not volume and sales (B3 and B5) item 1 2 3 4 a price 50 60 20 30 a volume 10 15 12 30 a u...

401k Manager Balances not matching.
I am using Money 2006. I updated new transactions into my 401k register from my fund website. It showed the new buys, but the end total was not correct, did not add in the new amounts. When I go to the 401k manager screen, the amounts are correct and the allocation between the funds is correct and the end total is correct. The investing home shows the incorrect amount from the register. Will this not update to the correct amount until the next statement or am I doing somehting wrong? TIA I am having the same issue, my 401K updates the daily change in my funds, so some days it shows me...

get picture based on index
Hello, I'm trying to figure out how to display one of several different "Word Drawing" objects, from an index (integer value) located in a cell. For illustrative purposes, imagine that I have created 6 drawings representing the faces of a die. I have a cell which creates a random number between 1 and 6. based on this index, i want to display one of the 6 drawings, to simulate the rolling of a die. Is it possible to do this without VB? In either case, what's the simplest way? Thank You! --Steven Hi not possible without VB (well AFAIK anyway) ... check out http://www...

Wrong matched transaction
I accidentally matched a downloaded transaction to the wrong registry entry (same amount but later date). Is is possible to unmatch and trigger an rematch? Bruce. Only way I have ever gotten this to work is delete the transaction and then redownload. The second download will have to be manual to get the item downloaded again. Art "Bruce Chastain" <bachastain@XNOSPAMXsbcglobal.net> wrote in message news:%23UA$F79HHHA.3676@TK2MSFTNGP03.phx.gbl... >I accidentally matched a downloaded transaction to the wrong registry entry >(same amount but later date). Is is ...

Indirect vs. Index
Hi, can someone explain to me what's the main difference between INDIRECT and INDEX? When should we use indirect and when should we use index? Thank you in advance. WJ They are two largely unrelated functions. Which to use depends on what you want to accomplish. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "WJ" <WJ@discussions.microsoft.com> wrote in message news:A0ADE44E-7709-4C85-A9B8-FD385300990E@microsoft.com... > Hi, can someone explain to me what's the main difference > between INDIRECT and ...

Number Matching
Hi I would be grateful if you could advise if there is a function that can do the following: I have a column with a list of numbers: a1 13 a2 16 a3 24 a4 19 etc and I need to find which nubers add uo to a fixed number eg 35 answer is a2+a4 The need is from an accounting point of view where there is a series of invoices outstanding with values to 2 decimal places. I appreciate that there may be several combinations in the result but if the assumption is made that there is only one is there a programatical way of checking? Kind regards Colin Hello Colin, MrExcel had a c...

Matching cells #5
Niek, Help! (See my original "matching cells" posting dated June 17). I've changed my spreadsheet: Column A (formerly containing 60,000 cells) is now Column B (containing 59,414 cells) and Column B is now Column I. Accordingly, I've changed your formula to: =IF(VLOOKUP(B1,$I$1:$I$59414,1)=B1,B1,"") But now, the data in Column I is NOT being moved down to the matching cells in Column B. What am I doing wrong? Thanks, Gary ...

Index/Match ... Excel has encountered an Error
Excel2003 WorkBook with many INDEX/MATCH Formulas ... Issue ... When I attempt to write a new INDEX/MATCH Formula I am having a problem with Excel each time I attempt to enter the "M": =Index(myrange,m ... at this point I receive pop-up stating I have an "Identify" conflict & I need to select a Cell to use ... Seems no matter what I do at this point I receive the Error message "Excel has encountered a problem & needs close". Above said, my work around has been to copy/paste an INDEX/MATCH formula from a different cell & then to edit ...

Hard to explain, but specific e-mail causing problems for my clien
I don't know how to explain this or label it, but let me do my best. This week, about half my users are experiencing weird errors with Outlook when they first get in. What happens is that their Outlook stops responding when they check their email and it only happens when they first get in in the morning. I've been trying to figure out what is going on and it has been difficult. However, I might have found something that could be causing the problems. We are a mortgage company and every morning, a person in our operations department sends out 3 Rate sheets in .pdf format. They ...

Finding a value from an Index?
Hi, I have a list of items that are selected on a regular basis right no in a drop down menu. I want to change this so that when I begin to typ the item, it will jump down to that item (similar to using the hel function in excel, you type "cou" and it jumps to count). So I have the list of items that I want to be looked up on a seperat tab, I was wondering how to go about putting this function in. Thanks -- guruma ----------------------------------------------------------------------- guruman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1592 View...

Vlookup, Index or Match
Hello All, I am using Office 2003 and have the following problem: I have a Sheet PROD (figures for example purpose only- it can be numeric or alpha numeric) Col B Col H 1 Mar-07 123 2 xx xx 3 xx xx 4 Mar-07 345 5 xx 6 Mar-07 678 7 Apr-07 1234 8 Apr-07 5678 9 xx xx 10 Apr-07 9101 .... .... .... Below there are other months also...Col B will go down for a complete year. i.e. probably 500 Rows down. I have a Sheet name REPORTS where I wish to have the information from Sheet PROD when I input the Month in Cell B6 on Sheet REPORTS...

What happened to the index in the Help menu?
The index used to be the only useful part of the Help menu in Excel. Now it doesn't seem to exist anymore in the Office 2003 version. Am I just not seeing it, or did they really take it out? It is gone. Use the answer wizard and type in your keyword. Gord Dibben Excel MVP On Thu, 29 Dec 2005 13:28:02 -0800, judanna77 <judanna77@discussions.microsoft.com> wrote: >The index used to be the only useful part of the Help menu in Excel. Now it >doesn't seem to exist anymore in the Office 2003 version. Am I just not >seeing it, or did they really take it out? ...

How to implement a read-only Index property for the members of a Collection?
Hi to all, After searching the net for possible solutions and not finding anything useful I came for your advice. I have a collection of objects with the properties X and Y, which are the coordinates of vertices (points). I need to add an Index property to those objects. I know that the actual index of each vertex in the vertices collection may change after adding to or removing elements from the collection. Is it possible to retrieve the actual index of each vertex in the collection and assign it to a read-only property, an Index property? I tried the code below. Notice th...

Indexing items from a pick list
I have a pick list which is sorted in alphabetic order. Is there a way that when the pick list seleted, I can say enter D and it automatically displays items within the pick list that starts with a D. Any help would be appreciated. That is what a combobox does. -- HTH RP (remove nothere from the email address if mailing direct) "Pank Mehta" <PankMehta@discussions.microsoft.com> wrote in message news:ACC97602-C1A0-4F20-AC27-A1CBD59094D9@microsoft.com... > I have a pick list which is sorted in alphabetic order. > > Is there a way that when the pick list selete...

Can't type in AnswerWizard or Index of code window help
Hi All, I run Access 2002 Developer. Recently, when I open help from visual basic code window, it won;t let me type text in the answer wizard or index search boxes. The icon switches to a horizontal, double arrow, as if I was trying to resize the pane. I can't figure out what stupid thing I've done. I sure miss help, though. Does this sound familiar to anyone? Help! - Max ...