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

IVR</SPAN>	2015</SPAN>	2080</SPAN>	2082</SPAN>	2089</SPAN>	2820</SPAN>	6003</SPAN>	6004</SPAN>
2002</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
2008</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
2036</SPAN>	1</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
2052</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	9</SPAN>	0</SPAN>
2053</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	44</SPAN>	0</SPAN>
2054</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
2072</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	3</SPAN>
2098</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	43</SPAN>	0</SPAN>
2835</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	416</SPAN>	1</SPAN>
3021</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	1</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
3023</SPAN>	0</SPAN>	1</SPAN>	2</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=7></COLGROUP>

 

[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

IVR</SPAN>	2015</SPAN>	2080</SPAN>	2082</SPAN>
2002</SPAN>	5</SPAN>	0</SPAN>	0</SPAN>
2008</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
2036</SPAN>	1</SPAN>	0</SPAN>	0</SPAN>
2052</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
2053</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>

<TBODY>
</TBODY>

Sheet 2	2006</SPAN>	2015</SPAN>
2002</SPAN>	=INDEX('01-03-2014'!A:A,MATCH(2002,A:A,0),MATCH(2006,1:1,0))</SPAN>	=INDEX('01-03-2014'!A:A,MATCH(2002,'01-03-2014'!A:A,0),MATCH(2015,'01-03-2014'!1:1,0))</SPAN>
2003</SPAN>	=INDEX('01-03-2014'!A:A,MATCH(2005,A:A,0),MATCH(2006,1:1,0))</SPAN>
2005</SPAN>	=INDEX('01-03-2014'!A:A,MATCH(2003,A:A,0),MATCH(2006,1:1,0))</SPAN>
2008</SPAN>	=INDEX('01-03-2014'!A:A,MATCH(2008,A:A,0),MATCH(2006,1:1,0))</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[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.....