Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Explaination of 3 Excel functions: Match, Index and Offset

Posted on 2011-03-03
Medium Priority
448 Views
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
Question by:rayluvs
• 9
• 8
• 4
• +1

LVL 30

Expert Comment

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

barry houdini earned 732 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

barry houdini earned 732 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

barry houdini earned 732 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

TerrySolanen earned 184 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

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

SiddharthRout earned 1084 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

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

SiddharthRout earned 1084 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

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

SiddharthRout earned 1084 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

Author Comment

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

Thanx!
0

LVL 30

Assisted Solution

SiddharthRout earned 1084 total points
ID: 35033858
Here it is :)

Sid
Example.xlsx
0

LVL 30

Expert Comment

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

Give me few moments

Sid
0

LVL 30

Assisted Solution

SiddharthRout earned 1084 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

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

SiddharthRout earned 1084 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

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

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

barry houdini earned 732 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

ID: 35035345
Thanx, fully understood!
0

Author Closing Comment

ID: 35035410
Thanx
0

## Featured Post

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month12 days, 5 hours left to enroll