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

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
###### Who is Participating?

Commented:
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

Commented:
If you check the Excel help, it explains in simple terms with examples :)

Have you gone through it?

Sid
0

Commented:
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

Commented:
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

Commented:
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 Commented:
I understand the MATCH, but INDEX and OFFSET still not in the clear.

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

Commented:
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 Commented:
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

Commented:
It is an array formula. You have to press Control + Shift + Enter after you enter the formula :)

Sid
0

Author Commented:
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

Commented:
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 Commented:
yes that would be great!  that'll give me an idea of the functions

Thanx!
0

Commented:
Here it is :)

Sid
Example.xlsx
0

Commented:
Oops that is Index + Match...

Give me few moments

Sid
0

Commented:
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 Commented:
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

Commented:
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

Commented:
>>>>I gave Cell A1 has 1001. What would

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

Sid
0

Author Commented:
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

Commented:
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 Commented:
Thanx, fully understood!
0

Author Commented:
Thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.