Find a value from the first column of sheet B using 2 values from sheet A. Some cells have a number range (EX: 19-20)

[GDtoBMe]

I'm relearning (and learning new) Excel skills. I've been able to complete everything on the attached sample file except for F2:F15. Really, I just need the formula for F2 (Subtest SC) and I will figure out the rest (good learning experience for me).

In short, F2:F15 are found by using the values from C2 (Age is the same for each Subtest in the list) and E (Raw Score for each subtest, which is entered manually).

The formulas I tried are in:

• J2: I didn't expect it to work though because of the number ranges like SC!B9).
• J3: Same formula but pointing to the SC_Long sheet. If necessary (or easier) I can extend each sheet with number ranges in cells just like I did for SC.
  • Just to test my formula I extended the SC worksheet so no number ranges existed (SC_Long).
• As an example:
  • Find the Score of a child that is 5.0 years old (C2) and has a Raw Score of 24 (E2). The array (?) should find SC!B6 (5.0 and 24) and return the value from A6 (15).
    • If the child was 6.0 with a Raw Score of 24, it would find D9 and return the score from A9.

To accomdate the Mini-sheet. I condensed it all on 1 sheet.

Once again, any assistance is greatly appreciated.

Sample_Scoring_File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1			Age	SubTest	Raw Score	Standard Score	Percentile	SD					Score	5.0	5.6	6.0	6.6	7.0	8.0
2			5.0	SC	24	15	95	+2 SD		#N/A			19
3	Subtest Scaled Score			WS	26	13	84	+1 SD		#N/A			18	26
4				FS	12	11	63	+1 SD					17		26
5				RS	6	6	9	-1 SD					16	25		26	26
6				WC	8	8	25	Within +/- 1 SD					15	24	25
7				FD	23	18	99.6	+3 SD					14	23	24	25	25	26
8				LC	16	8	25	Within +/- 1 SD					13	22	23				26
9				PP	152	9	37	Within +/- 1 SD					12	20-21	22	24	24	25
10				USP	20	19	99.9	+3 SD					11	18-19	21	23			25
11				RC									10	16-17	19-20	21-22	23	24
12				SW									9	14-15	17-18	19-20	22	23	24
13				WD									8	11-13	15-16	17-18	20-21	22
14				SA									7	9-10	12-14	15-16	18-19	21	23
15				SR									6	6-8	9-11	12-14	15-17	19-20	21-22
16													5	4-5	6-8	9-11	12-14	16-18	19-20
17						Subtest Total	Percentile	SD					4	1-3	4-5	6-8	9-11	14-15	16-18
18	Core Language					45	<0.1	-3 SD					3	0	2-3	4-5	7-8	11-13	14-15
19	Receptive					154	>99.9	+3 SD					2		1	3	5-6	8-10	11-13
20	Expressive					121	92	+1 SD					1		0	0-2	0-4	0-7	0-10
Calculations

Cell Formulas
Range	Formula
G2:G15	G2	=IFERROR(INDEX(Stanine[Percentile],MATCH(F2,Stanine[Score],0)),"")
H2:H15	H2	=IFERROR((IF(F2="","",INDEX(Subtest_SD[Distance],MATCH(F2,Subtest_SD[Lookup],-1)))),"")
J2	J2	=INDEX(SC[Score],MATCH(C2,SC[#Headers],0),MATCH(E2,SC[[5.0]:[8.0]],0))
J3	J3	=INDEX(SC_long[Score],MATCH(C2,SC_long[#Headers],0),MATCH(E2,SC_long[[5.0]:[8.0]],0))
F18	F18	=F2+F3+F4+F5
G18:G20	G18	=IFERROR(INDEX(Stanine[Percentile],MATCH(F18,Stanine[CI_Score],0)),"")
H18:H20	H18	=IFERROR((IF(F18="","",INDEX(CI_SD[Distance],MATCH(F18,CI_SD[Lookup],-1)))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S2:S20	Cell	contains a blank value	text	NO
R2:R20	Cell	contains a blank value	text	NO
Q2:Q20	Cell	contains a blank value	text	NO
P2:P20	Cell	contains a blank value	text	NO
O2:O20	Cell	contains a blank value	text	NO
N2:N20	Cell	contains a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2	List	5.0, 5.6, 6.0, 6.6, 7.0, 8.0,9.0,10.0,11.0,12.0,13.0,14.0,15.0,16.0,17.0-21.11

Tom

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMIFS(SC[Score],INDEX(SC[[5.0]:[8.0]],,MATCH(C2,SC[[#Headers],[5.0]:[8.0]]+0,0)),E2&"")

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find a value from the first column - 2 values, 1 is a number range (EX: 19-20)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[GDtoBMe]

Hi & welcome to MrExcel.
How about

=SUMIFS(SC[Score],INDEX(SC[[5.0]:[8.0]],,MATCH(C2,SC[[#Headers],[5.0]:[8.0]]+0,0)),E2&"")

Thanks, Fluff! That worked perfectly for cells will a single value. Any suggestion on how I can handle a cell with a number range (N9 has 20-21 or N6 has 6-8)? If the easy (right?) solution is to just extend all of my tables to edit the cells with a range and have only 1 number per cell, I'm completely good with that.

 

[Fluff]

It already handles that

Sample_Scoring_File.xlsx
	A	B	C	D	E	F	G
1	Score	5.0	5.6	6.0	6.6	7.0	8.0
2	19
3	18	26
4	17		26
5	16	25		26	26
6	15	24	25
7	14	23	24	25	25	26
8	13	22	23				26
9	12	20-21	22	24	24	25
10	11	18-19	21	23			25
11	10	16-17	19-20	21-22	23	24
12	9	14-15	17-18	19-20	22	23	24
13	8	11-13	15-16	17-18	20-21	22
14	7	9-10	12-14	15-16	18-19	21	23
15	6	6-8	9-11	12-14	15-17	19-20	21-22
16	5	4-5	6-8	9-11	12-14	16-18	19-20
17	4	1-3	4-5	6-8	9-11	14-15	16-18
18	3	0	2-3	4-5	7-8	11-13	14-15
19	2		1	3	5-6	8-10	11-13
20	1		0	0-2	0-4	0-7	0-10
SC

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G20	Cell	contains a blank value	text	NO
F2:F20	Cell	contains a blank value	text	NO
E2:E20	Cell	contains a blank value	text	NO
D2:D20	Cell	contains a blank value	text	NO
C2:C20	Cell	contains a blank value	text	NO
B2:B20	Cell	contains a blank value	text	NO

Sample_Scoring_File.xlsx
	A	B	C	D	E	F	G	H	I	J
1			Age	SubTest	Raw Score	Standard Score	Percentile	SD
2			5.0	SC	20-21	15	95	+2 SD		12
3	Subtest Scaled Score			WS	26	13	84	+1 SD
4				FS	12	11	63	+1 SD
5				RS	6	6	9	-1 SD
6				WC	8	8	25	Within +/- 1 SD
7				FD	23	18	99.6	+3 SD
8				LC	16	8	25	Within +/- 1 SD
9				PP	152	9	37	Within +/- 1 SD
10				USP	20	19	99.9	+3 SD
11				RC
12				SW
13				WD
14				SA
15				SR
16
Calculations

Cell Formulas
Range	Formula
G2:G15	G2	=IFERROR(INDEX(Stanine[Percentile],MATCH(F2,Stanine[Score],0)),"")
H2:H15	H2	=IFERROR((IF(F2="","",INDEX(Subtest_SD[Distance],MATCH(F2,Subtest_SD[Lookup],-1)))),"")
J2	J2	=SUMIFS(SC[Score],INDEX(SC[[5.0]:[8.0]],,MATCH(C2,SC[[#Headers],[5.0]:[8.0]]+0,0)),E2&"")

Cells with Data Validation
Cell	Allow	Criteria
C2	List	5.0, 5.6, 6.0, 6.6, 7.0, 8.0,9.0,10.0,11.0,12.0,13.0,14.0,15.0,16.0,17.0-21.11

 

[GDtoBMe]

Ah!!! I see. If the Raw Score (E2) is entered as 20-21 (N9), it's perfect. That's my mistake. I didn't explain that only 1 number would be entered. In this example, 20 or 21, but not 20-21.

When 20 is entered, the result is 0 (because it's looking for an exact match), but the correct Score is 12 (M9).

Thoughts?

 

[Fluff]

If you want to put 20 or 21 into E2, then you would bet better of changing your SC table to individual numbers rather than a range.

 

[GDtoBMe]

Perfect. A clear and direct answer. Thank you for everything, Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.