# formula problems (now with graphics!!)

#### salopian

##### New Member
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
you are finding the 1 in columnB and then the 1 in c6:g6 but what data are you trying to return ?

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>

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
ABCDEFGHIJK
3
4
51AAA1111111
62BBB1221112
73CCC1331114

<tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
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

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

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.

Replies
0
Views
142
Replies
5
Views
372
Replies
7
Views
271
Replies
1
Views
302
Replies
2
Views
202

1,219,819
Messages
6,150,405
Members
450,960
Latest member
GB2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back