Solved

Explaination of 3 Excel functions: Match, Index and Offset

Posted on 2011-03-03
22
434 Views
Last Modified: 2012-06-21
Can an EE explain in easy to understand terms each of the following functions:

   1. Match
   2. Index
   3. Offset

Please include an example of each
0
Comment
Question by:rayluvs
  • 9
  • 8
  • 4
  • +1
22 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35032097
If you check the Excel help, it explains in simple terms with examples :)

Have you gone through it?

Sid
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 183 total points
ID: 35032117
Excel help covers these fairly well, that would be my start point.....

MATCH returns a number of the lookup value in a row or column, e.g.

=MATCH("x",B3:B7,0)

will return 1 if the first instance of "x" is in the first cell in the range, i.e. B3...or 2 if it's in the 2nd (B4) etc. I used zero as the final argument, that denotes an exact match so if "x" isn't found at all then you get #N/A error. You can use 1 or -1 as the third argument also, these are for data sorted ascending and descending respectively...and a "closest match" may be returned.

I'll let somebody else do INDEX and OFFSET......!

barry
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 183 total points
ID: 35032163
Ok, perhaps not.....

INDEX is fairly simple in its most basic form

=INDEX(B3:B7,2)

that will return the 2nd value in B3:B7, i.e. B4. Note that if used on it's own that formula will just return the value in B4, but used in the right context it can also return a cell reference, e.g.

=B1:INDEX(B3:B7,2)

that returns the range B1:B4 which can be used in other functions

If the INDEX range is a matrix (multiple rows and columns) then you specify the row number and column number

=INDEX(A1:J10,4,5) returns the value (or reference) at the intersection of the 4th row and 5th column of the range A1:J10, in this case E4 or its contents.

If either argument is zero then the whole column or row is returned (useful for use in other functions)

There is a 4th argument of INDEX which allows you to point to a range to be used if you specify multiple ranges in the first argument, see Excel help.....

barry
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 183 total points
ID: 35032202
OFFSET lets you specify a range from a start point,

=OFFSET(A1,1,2,3,4)

That sets up A1 as the start point, ist argument (1) moves it down 1 row and second argument moves it across 2 columns (so start point, i.e. top left,  of returned range is C2). 3 specifies the height and 4 the width so the range would be C2:F4.

Again that might not be useful on it's own but you can use in another function, so

=SUM(OFFSET(A1,1,2,3,4))

gives the sum of C2:F4

Those are the basics.......

Those 3 functions can be put to many different and complex uses especially when used together or with other functions - do you have a specific problem to address?

barry
0
 
LVL 1

Assisted Solution

by:TerrySolanen
TerrySolanen earned 46 total points
ID: 35032834
MATCH: answers the question, "In this list, which row does the value "x" first appear?"

INDEX: answers the question, "In that list, give me the value of row n."

OFFSET: "starting from that point over there, move down g rows, and over h columns...and give me the value of that cell."

I was never very good at looking at formulas and understanding them, so the attached file has the examples.
Excel--Index--Match--Offset.xls
0
 

Author Comment

by:rayluvs
ID: 35033088
I understand the MATCH, but INDEX and OFFSET still not in the clear.

In what real-world practice can we use these functions?
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 271 total points
ID: 35033108
The INDEX / MATCH functions can be used together, as a flexible and powerful tool for extracting data from a table.

See this example

http://support.microsoft.com/kb/214142

Sid
0
 

Author Comment

by:rayluvs
ID: 35033231
Thanx... thats exactly why we placed the question,.  We do a lot of data extraction and one of our colleagues pointed out the use of these functions.

We did go into the help and googled it, but the INDEX and OFFSET is still unclear.  

We did try your link, but the example doesn't really explains the use and I did try the example but doesn't give the result it should (see file included).


ExcelMatch-IndexSample.xlsx
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 271 total points
ID: 35033282
It is an array formula. You have to press Control + Shift + Enter after you enter the formula :)

Sid
0
 

Author Comment

by:rayluvs
ID: 35033400
you're right, I was pressing CTRL-SHIFT-ENTER after entering the formula.

Based on your experience, how have you used the INDEX and OFFSET for data extraction?

0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 271 total points
ID: 35033406
Honestly, I am more of a VBA guy. I prefer VBA over formulas. But yes, I have used INDEX and OFFSET. Do you want me to create a sample for you for INDEX + OFFSET for data extraction?

Sid
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:rayluvs
ID: 35033822
yes that would be great!  that'll give me an idea of the functions

Thanx!
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 271 total points
ID: 35033858
Here it is :)

Sid
Example.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033861
Oops that is Index + Match...

Give me few moments

Sid
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 271 total points
ID: 35033899
Here it is

However, I would still request you to go though Barry's post above. He has explained each of the functions separately. Rest will only come from practice :)

Sid
Example.xlsx
0
 

Author Comment

by:rayluvs
ID: 35033995
I think I got pretty much understanding of MATCH and INDEX, yet I'm still tryinh to grasp OFFSET.

Can you explain cell D14 in your example?  I don't see no references to that cell, yet "Dancing" does appear when employee is 1003.
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 271 total points
ID: 35034031
Offset is One thing set off or developed from something else.

See Barry's post ID: 35032202. Forget all the formulas for 1 moment and concentrate on that post.

Tell me what do you understand from it? Or better still, lets say in the file that I gave Cell A1 has 1001. What would

=OFFSET(A4,1,1,1,1)

give me? And Why?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35034038
>>>>I gave Cell A1 has 1001. What would

Sorry I meant to say "I gave Cell A4 has 1001. What would..."

Sid
0
 

Author Comment

by:rayluvs
ID: 35034265
Barry's post describes it technically as to what the function does, but when I see your Excel, I still can't understand why a value appears when no reference to that cell has been made.

For example, your "=OFFSET(A4,1,1,1,1) ", gives "SiddharthRout".  

I guess my question is why the cell coordinate of "SiddharthRout" is not within the  "=OFFSET(A4,1,1,1,1)" and yet it gives "SiddharthRout".  
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 183 total points
ID: 35034945
If you use

=OFFSET(A4,1,1,1,1)

Then the first 1 is the number of rows (down) to move A4 so 1 means it moves down 1 to A5.....the next 1 is the number of columns to move (right), so A5 becomes B5.....so that formula simply retrieves the value 1 row down and 1 column across from A4, the value in B5.

For looking up and retrieving data you can do almost everything that you can do with OFFSET with INDEX......and INDEX is prefearble because OFFSET is a "volatile" function. That means that it re-calculates every time the sheet is re-calculated - even if none of the reference data changes (unlike most other functions).....so it can cause performance issues if you have large numbers of OFFSET functions in a sheet.

The most basic use of INDEX and MATCH is to MATCH data in one column and then return a value from another, VLOOKUP can do the same but INDEX/MATCH has several advantages.

1. It can return data from a column to the left of the lookup range (VLOOKUP can't).
2. It's more robust than VLOOKUP when columns or rows are added or deleted in your data
3. It can be more easily manipulated to, for instance, match in one row and return data from a row up or a row down or whatever is required.

In your example you are doing something more complex, matching two columns and returning data from a third. The way you are doing that is valid but there are other methods, you can avoid "array entering" (CTRL+SHIFT+ENTER) for instance by using this version

=INDEX($C$2:$C$5,MATCH(1,INDEX((D2=$A$2:$A$5)*($B$2:$B$5=E2),0),0))

or even using LOOKUP

=LOOKUP(2,1/(D2=$A$2:$A$5)/($B$2:$B$5=E2),$C$2:$C$5)

regards, barry

0
 

Author Comment

by:rayluvs
ID: 35035345
Thanx, fully understood!
0
 

Author Closing Comment

by:rayluvs
ID: 35035410
Thanx
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ifna and iferror 1 32
Macro 6 48
Configure Sharepoint 2013 to allow Excel files to be edited online 9 52
remove lower case characters in excel formula 12 22
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now