Solved

Explaination of 3 Excel functions: Match, Index and Offset

Posted on 2011-03-03
22
432 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
It is an array formula. You have to press Control + Shift + Enter after you enter the formula :)

Sid
0
 

Author Comment

by:rayluvs
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rayluvs
Comment Utility
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
Comment Utility
Here it is :)

Sid
Example.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Oops that is Index + Match...

Give me few moments

Sid
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 271 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>>>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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanx, fully understood!
0
 

Author Closing Comment

by:rayluvs
Comment Utility
Thanx
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 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

15 Experts available now in Live!

Get 1:1 Help Now