This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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

1. Match

2. Index

3. Offset

Please include an example of each

Have you gone through it?

Sid

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

=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

=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

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

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

See this example

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

Sid

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

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

Sid

Sid

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

Sid

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

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.

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

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

Sid

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".

=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,I

or even using LOOKUP

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

regards, barry

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.

All Courses

From novice to tech pro — start learning today.

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