IF criteria based on value of another field

[adamcfishman]

This is what I am trying to do:
1 Sample, 0 Errors = A
1 Sample, 1 Error = D
2 Samples, 0 Errors = A
2 Samples, 1 Error = C
2 Samples, 2 Errors = D
3 Samples, 0 Errors = A
3 Samples, 1 Error = B
3 Samples, 2 Errors = C
3 Samples, 3 Errors = D
4-10 = 0 errors = A, 1 Error = B, 2 Errors = C, 3 or more Errors = D

The formula i currently have just marks as N/A if less than 3.
=IF(N4<4,"N/A",IF(N4>9,IF(O4=0,"A",IF(O4=1,"B",IF(O4=2,"C","D"))),IF(AND(N4>3,N4<10),IF(O4=0,"A",IF(O4=1,"C","D")))))

[Tony Valko]

Create a table like this...

Data Range

	A	B	C	D	E	F	G	H
1	Samples	Errors	Grade			Samples	Errors	Grade
2	1	0	A			2	2	D
3	1	1	D
4	2	0	A
5	2	1	C
6	2	2	D
7	3	0	A
8	3	1	B
9	3	2	C
10	3	3	D

This array formula** entered in H2:

=INDEX(C2:C10,MATCH(F2,IF(B2:B10=G2,A2:A10),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[adamcfishman]

That is great, it only works up to four samples we need it to go through up to 10 samples. Also is the a formula as opposed to a array as this will need to be put into aprox 10k cells which would take quite a while to hit on all of them.

Thank you again for any help you can give.

[adamcfishman]

That is great, it only works up to four samples we need it to go through up to 10 samples. Also is the a formula as opposed to a array as this will need to be put into aprox 10k cells which would take quite a while to hit on all of them.

Thank you again for any help you can give.

[Tony Valko]

You have to expand the table as needed.

That is an array formula but it's fairly efficient. Try it on your full data set and see how it works.