Excel Formula

The INDEX function

The basic INDEX function returns a VALUE based on a defined array / column and a row number.  The syntax from Excel is as follows:

=INDEX ( array , row number )

Below is an example of using INDEX to return the value “Wallet,” assuming that you already know that the value is three cells down on your defined array.

Formula: =INDEX(B2:B6,2)

 

	A	B	C
1	ID	Product	Quantity
2	101	Watch	5
3	128	Wallet	12
4	206	Bag	200
5	212	Shoes	8
6	237	Belt	2
7	Lookup	Result
8	128	Wallet

 

The MATCH function

The basic MATCH function returns a NUMBER based on the relative position of a lookup value within a defined array / column.  The syntax from Excel is as follows:

=MATCH (  lookup value , lookup array , match type )

Below is an example of using the MATCH formula to return the position of “206″ within our column reference.

Formula: =MATCH(A8, A2:A6, 0)

 

	A	B	C
1	ID	Product	Quantity
2	101	Watch	5
3	128	Wallet	12
4	206	Bag	200
5	212	Shoes	8
6	237	Belt	2
7	Lookup	Result
8	206	3

Since “206″ is 3th cells down in the array, the value “3″ is returned.

INDEX MATCH

When we combine both the INDEX formula and the MATCH formula, the number that the MATCH formula returns becomes the row number for your INDEX formula.

=INDEX ( array , MATCH formula )

=INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0″ ))

 

Below is an example of using the INDEX MATCH to return "Quantity” for our lookup value.

As you can see, it returns the same value we got from VLOOKUP.

Formula: =INDEX(C2:C6, MATCH(A8, A2:A6, 0))

	A	B	C
1	ID	Product	Quantity
2	101	Watch	5
3	128	Wallet	12
4	206	Bag	200
5	212	Shoes	8
6	237	Belt	2
7	Lookup	Result
8	206	200

The VLOOKUP function

First our point of reference: the trusty VLOOKUP formula.  VLOOKUP returns a VALUE based on a defined array and column reference.  The syntax from Excel is as follows:

=VLOOKUP ( lookup value , lookup array , column , range lookup )

Below is an example of using VLOOKUP to return the value “200” based on the lookup value “206”

Formula: =VLOOKUP(A8, A2:C6, 3, FALSE)

 

	A	B	C
1	ID	Product	Quantity
2	101	Watch	5
3	128	Wallet	12
4	206	Bag	200
5	212	Shoes	8
6	237	Belt	2
7	Lookup	Result
8	206	200

----------------------------------------------------------------------------------------------------------------------- 

A	B	C	D
Ali		Susan	Yes
Susan		Meng	No
May		Yee	No
Mutu		Ahmad	No
Joe		Ali	Yes

 To check is the variable in column C existed in the column A (row 1-5) list.
 [column D]
    =IF(ISERROR(MATCH(C1,$A$1:$A$5,0)),"No","Yes")

A	B	C	D
Ali	13	Susan	20
Susan	20	Meng
May	7	Yee
Mutu	34	Ahmad
Joe	56	Ali	13

To get the pairing variable for variable in column C from table A1:B5
[Column D]
 =IF(ISERROR(VLOOKUP(C1,$A$1:$B$5,2,FALSE)),"",VLOOKUP(C1,$A$1:$B$5,2,FALSE))