How to use INDEX function - part one

We have seen how to use VLOOKUP in the earlier posts,

http://newexceltips.blogspot.sg/2012/07/how-to-use-column-and-columns-functions.html and 

http://newexceltips.blogspot.sg/2012/05/how-to-use-concatenate-to-create-tag-or.html.

In the same way we can use HLOOkUP (vertical and horizontal).


But if we need to use vertical and horizontal look up at the same time, we need to use INDEX. 
Lets say we have table like below,

and say we need to make a table like below,

In the cell J3, type =INDEX( and choose 

As you can see we need an array (table), row number and column number. To find corresponding row number and column number we need to use Formula "MATCH".

for example 

Row number MATCH(H3,A3:A11,0)
Column number MATCH(I3,B2:E2,0)

Lets look how the formula looks like,

Note the row number and column number needed are of the array (table) not of the worksheet.


So here is the formula,
=INDEX(B3:E11,MATCH(H3,A3:A11,0),MATCH(I3,B2:E2,0))

As usual we need to lock the cells.
=INDEX($B$3:$E$11,MATCH($H3,$A$3:$A$11,0),MATCH($I3,$B$2:$E$2,0))

Then drag the the formula to the cells as needed.



We can also use as array formula. 
=INDEX($B$3:$E$11,MATCH(H3:H5,$A$3:$A$11,0),MATCH(I3:J5,$B$2:$E$2,0))
Remember to use Shift+Ctrl+Enter

If we are expecting wrong Machine or Data (accidental or otherwise) then we can add a 

IFERROR (Excel 2007 onwards).
=IFERROR(INDEX($B$3:$E$11,MATCH($H6,$A$3:$A$11,0),MATCH($I6,$B$2:$E$2,0)),"NO VALUE")

or ISERROR (Old versions of Excel)
=IF(ISERROR(INDEX($B$3:$E$11,MATCH($H6,$A$3:$A$11,0),MATCH($I6,$B$2:$E$2,0)))=TRUE,"NO VALUE",INDEX($B$3:$E$11,MATCH($H6,$A$3:$A$11,0),MATCH($I6,$B$2:$E$2,0)))

  

That's it. Your comments are always welcome.