formula problems (now with graphics!!)

[salopian]

I have 2 worksheets and I need some help, please!

	COL A	COL B	COL E	COL F	COL G	COL H	COL I	COL J	COL L
ROW 1
ROW 2	TASK	UNIT	FREQ	DIFF	PC	RISK 1	RISK 2	RISK
ROW 3
ROW 4
ROW 5	1	AAA	1	1	1	1	1	1
ROW 6	2	BBB	1	2	2	1	1	1
ROW 7	3	CCC	1	3	3	1	1	1

<TBODY>
</TBODY>

In Worksheet 1 (above) I require a number added to Column L that is calculated from the data in Columns G and J against the data in the second Worksheet entitled TNPA.

In the example above L5 should read 1; L6 should read 2 and L7 should read 4.

I applied the formula =IFERROR(INDEX(TNPA!$C$2:$G$6,MATCH(G5,TNPA!$B$2:$B$6,0),MATCH(J5,TNPA!$C$7:$G$7,0)),"")

but it returns nothing!

</SPAN></SPAN>

	COL A	COL B	COL C	COL D	COL E	COL F	COL G
ROW 1
ROW 2	PC	5	9	10	11	12	13
ROW 3	PC	4	6	7	8	10	12
ROW 4	PC	3	4	5	7	8	11
ROW 5	PC	2	2	3	5	7	10
ROW 6	PC	1	1	2	4	6	9
ROW 7			1	2	3	4	5+
ROW 8			RISK	RISK	RISK	RISK	RISK

<TBODY>
</TBODY>

TNPA Worksheet

Any help or ideas greatly appreciated!
Many thanks

 

[oldbrewer]

you are finding the 1 in columnB and then the 1 in c6:g6 but what data are you trying to return ?

 

[salopian]

you are finding the 1 in columnB and then the 1 in c6:g6 but what data are you trying to return ?

In worksheet 1 row 5 column G (PC) = 1 and column J (RISK) = 1. <o></o>
The data i'm trying to return comes from worksheet 2. In this instance PC = 1 and Risk = 1 so the data is from Row 6, Column C (the red data) = 1 <o></o>

 

[Weazel]

your formula appears to be working just fine the way you have it.

You might want to check the lookup range and lookup values and make sure they are all the same data type

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K
2	TASK	UNIT			FREQ	DIFF	PC	RISK 1	RISK 2	RISK
3
4
5	1	AAA			1	1	1	1	1	1	1
6	2	BBB			1	2	2	1	1	1	2
7	3	CCC			1	3	3	1	1	1	4

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula K5 =INDEX(TNPA!$C$2:$G$6,MATCH(G5,TNPA!$B$2:$B$6,0),MATCH(J5,TNPA!$C$7:$G$7,0)) <tbody> </tbody>

<tbody>
</tbody>

Excel 2012

	A	B	C	D	E	F	G
2	PC	5	9	10	11	12	13
3	PC	4	6	7	8	10	12
4	PC	3	4	5	7	8	11
5	PC	2	2	3	5	7	10
6	PC	1	1	2	4	6	9
7			1	2	3	4	5
8			RISK	RISK	RISK	RISK	RISK

<tbody>
</tbody>

TNPA​

 

[salopian]

the data in worksheet 1 columns G & J is generated from furmalae using other coumns, could this impact my formula for colunmn L ?

 

[Weazel]

more then likely. If the values are being returned as text and the values column B and row 7 are numbers or vice versa then that could be your issue.

you could check the values with isnumber to verify how excel is seeing them then modify the 2 matches to be in the same format.

in the tables I posted I converted everything to numbers and it worked fine.