INDEX Function Multiple Syntax - 3

INDEX Function

---

About: Uses an index to choose a value from a reference or array



Category: Lookup
Location: Built-in

What Does It Do ?

---

This function picks a value from a range of data by looking down a specified number
of rows and then across a specified number of columns.
It can be used with a single block of data, or non-continuous blocks.

Syntax: There are various forms of syntax for this function.

Syntax 1

---

=INDEX(RangeToLookIn,Coordinate)
This is used when the RangeToLookIn is either a single column or row.
The Co-ordinate indicates how far down or across to look when picking the data from the range.

Both of the examples below use the same syntax, but the Co-ordinate refers to a row when
the range is vertical and a column when the range is horizontal.

Syntax 2

---

=INDEX(RangeToLookIn,RowCoordinate,ColumnColumnCordinate)
This syntax is used when the range is made up of rows and columns.

Syntax 3

---

=INDEX(NamedRangeToLookIn,RowCoordinate,ColumnColumnCordinate,AreaToPickFrom)
Using this syntax the range to look in can be made up of multiple areas.
The easiest way to refer to these areas is to select them and give them a single name.

The AreaToPickFrom indicates which of the multiple areas should be used.

Lets Understand With Example

---

In the following example the figures for North and South have been named as one
range called NorthAndSouth.


This is an extended version of the previous example.
It allows the names of products and the quarters to be entered.
The =MATCH() function is used to find the row and column positions of the names entered.
These positions are then used by the =INDEX() function to look for the data.

  =INDEX(EastAndWest,MATCH(F100,C91:C93,0),MATCH(F101,D90:G90,0),IF(F102=C90,1,IF(F102=C95,2)))

---

Share This On:

Match Formula

MATCH Formula

---

About :Looks up values in a reference or array

Category : Lookup

Location : Built-In

Match Formula

What Does It Do ?

---

This function looks for an item in a list and shows its position.
It can be used with text and numbers.

It can look for an exact match or an approximate match.

Syntax
=MATCH(WhatToLookFor,WhereToLook,TypeOfMatch)

The TypeOfMatch either 0, 1 or -1.


Using 0 will look for an exact match. If no match is found the #NA error will be shown.

Using 1 will look for an exact match, or the next lowest number if no exact match exists.
   If there is no match or next lowest number the error #NA is shown.
   The list of values being examined must be sorted for this to work correctly.

Using -1 will look for an exact match, or the next highest number if no exact match exists.
   If there is no exact match or next highest number the error #NA is shown.

   The list must be sorted for this to work properly.

Examples 1

---

Using the 0 option suitable for an exact match.
The Ascending list gives the exact match.
The Descending list gives the exact match.
The Wrong Value list cannot find an exact match, so the #NA is shown.

Example Match -1

=MATCH(F8,F3:F6,0)





Examples 2

---

Using the 1 option suitable for a ascending list to find an exact or next lowest match.
The Ascending list gives the exact match.
The Descending list gives the #NA error.
The Wrong Value list finds the next lowest number..

Example Match_2

 =MATCH(D8,D3:D6,1)






Examples 3

---

Using the -1 option suitable for a descending list to find an exact or next highest match.
The Ascending list gives the #NA error.
The Descending list gives the exact match.
The Wrong Value list finds the next highest number.






 =MATCH(B8,B3:B6,-1)



Examples 4

---

The tables below were used to by a bus company taking booking for bus tours.
They need to allocate a bus with enough seats for the all the passengers.
The list of bus sizes has been entered in a list.
The number of passengers on the tour is then entered.
The =MATCH() function looks down the list to find the bus with enough seats.
If the number of passengers is not an exact match, the next biggest bus will be picked.
After the =MATCH() function has found the bus, the =INDEX() function has been used
to look down the list again and pick out the actual bus size required.

Example Match_4

 =INDEX(D3:D7,MATCH(D9,C3:C7,-1),0))





Examples 5

---

The tables below were used by a school to calculate the exam grades for pupils.
The list of grade breakpoints was entered in a list.
The pupils scores were entered in another list.
The pupils scores are compared against the breakpoints.
If an exact match is not found, the next lowest breakpoint is used.
The =INDEX() function then looks down the Grade list to find the grade.

Example Match_5

 =INDEX(C3:C6,MATCH(F6,B3:B6,1),0)

---

Share This On: