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.

How to use #N/A to create line plots look more presentable

Let's look at the example below. The table showing rejects (column B), output (column C) and reject percentage (column D). Reject percentage = B2/C2.

Day	Rejects	Output	Reject Parentage
1	5	300	1.67%
2	80	400	20.00%
3	30	350	8.57%
4	0	0	#DIV/0!
5	45	455	9.89%
6	67	340	19.71%
7	43	360	11.94%
8	89	400	22.25%
9	23	320	7.19%
10	56	330	16.97%

Note on the fourth day there was no production. So we get an error  in the reject percentage column. If we plot the reject percentage, We will get,

On day four there is no production and so no rejects. 
The plot is not accurate as it shows reject percentage as zero. 

Ideally the plot should ignore day 4. To achieve this we need to change the formula in column D.

=IF(ISERROR(B2/C2)=TRUE,#N/A,B2/C2)

Now the table looks like,

Day	Rejects	Output	Reject Parentage
1	5	300	1.67%
2	80	400	20.00%
3	30	350	8.57%
4	0	0	#N/A
5	45	455	9.89%
6	67	340	19.71%
7	43	360	11.94%
8	89	400	22.25%
9	23	320	7.19%
10	56	330	16.97%

Now plot the reject percentage again.

Now the plot ignores day 4 and it shows no data point on day 4.

As always love to have your comments. 

How to avoid BLANKS / errors in CHARTS

Prerequisite 

Able to use/create charts in Excel. Know how to use "ISERROR" formula. Please refer to Microsoft help for Syntax.

Possible Usage

To reflect a clean and accurate in formation in charts.

For example, refer to the table below.

If we plot Reject Percentage Vs Date we will get,

First the chart is not accurate as there was no production in certain days and reject rate cannot be considered "Zero".

Second the chart is not tidy. 

The steps (How to do)

1. type =IF(ISERROR(C2/B2)=TRUE,#N/A,C2/B2) in D2

2. Drag or Copy/Paste all the necessary cells

3. We will get a clean and accurate chart

DONE