Choosing the correct Excel Formula

[sikhwizard]

Hi,

Can some one guide me in creating a formula to get data from a spreadsheet. Effectively, what I am trying to do is;


If a row in column IVR = 2002,
and if the column = 2006,
Find value (Content) in that cell.

Anyone tell me if a IF function, or Index is the best way forward.

Thanks

[TABLE="width: 483"]
<TBODY>[TR]
[TD]IVR</SPAN>[/TD]
[TD="align: right"]2015</SPAN>[/TD]
[TD="align: right"]2080</SPAN>[/TD]
[TD="align: right"]2082</SPAN>[/TD]
[TD="align: right"]2089</SPAN>[/TD]
[TD="align: right"]2820</SPAN>[/TD]
[TD="align: right"]6003</SPAN>[/TD]
[TD="align: right"]6004</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2002</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2008</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2036</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2052</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]9</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2053</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]44</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2054</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2072</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2098</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]43</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2835</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]416</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3021</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3023</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=7></COLGROUP>[/TABLE]

 

[steve the fish]

Assuming your data is placed in A1:H12 put this formula elsewhere:

=INDEX(A1:H12,MATCH(2002,A1:A12,0),MATCH(2006,A1:H1,0))

This at present will produce #N/A error as 2006 isn't present in your data!

 

[sikhwizard]

Thanks 'steve the fish'.

I was also trying to do it this way - Pulling data from one sheet to another so it just enters the cell contents.

=IF('01-03-2014'!A:A=2002 AND('01-03-2014'!1:1=2006), CELL("contents")

Like you said, it wouldnt match as I dont have a value of 2006.

I have changed this to =INDEX('01-03-2014'!A:A,MATCH(2002,'01-03-2014'!A:A,0),MATCH(2015,'01-03-2014'!1:1,0)) but it isnt pulling a value from there. [Hope that makes sense]



Sheet 01-03-2014

[TABLE="width: 227"]
<TBODY>[TR]
[TD]IVR</SPAN>
[/TD]
[TD="align: right"]2015</SPAN>
[/TD]
[TD="align: right"]2080</SPAN>
[/TD]
[TD="align: right"]2082</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2002</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2008</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2036</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2052</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2053</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="width: 1181"]
<TBODY>[TR]
[TD]
Sheet 2



[/TD]
[TD]2006</SPAN>[/TD]
[TD]2015</SPAN>[/TD]
[/TR]
[TR]
[TD]2002</SPAN>[/TD]
[TD]=INDEX('01-03-2014'!A:A,MATCH(2002,A:A,0),MATCH(2006,1:1,0))</SPAN>[/TD]
[TD]=INDEX('01-03-2014'!A:A,MATCH(2002,'01-03-2014'!A:A,0),MATCH(2015,'01-03-2014'!1:1,0))</SPAN>[/TD]
[/TR]
[TR]
[TD]2003</SPAN>[/TD]
[TD]=INDEX('01-03-2014'!A:A,MATCH(2005,A:A,0),MATCH(2006,1:1,0))</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2005</SPAN>[/TD]
[TD]=INDEX('01-03-2014'!A:A,MATCH(2003,A:A,0),MATCH(2006,1:1,0))</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2008</SPAN>[/TD]
[TD]=INDEX('01-03-2014'!A:A,MATCH(2008,A:A,0),MATCH(2006,1:1,0))</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[steve the fish]

The first part of the index equation needs to be the whole table. You have changed it to A:A. They need to be cell references ie A1:H12 for example. The match parts also need this type of reference. A:A or 1:1 wont work.

 

[BGY23]

Hi
The first part of the Index has to be a range not just a column so Index(A:A so shouldn't it at least be index(A:D ?

 

[BGY23]

Hi Again,

You can still use the A:A and 1:1 in the match formulas if you wish.

 

[sikhwizard]

Really Appreciate the help guys.....