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

New Member
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRS
1AgeSubTestRaw ScoreStandard ScorePercentileSDScore5.05.66.06.67.08.0
25.0SC241595+2 SD#N/A19
3Subtest Scaled ScoreWS261384+1 SD#N/A1826
4FS121163+1 SD1726
5RS669-1 SD16252626
6WC8825Within +/- 1 SD152425
7FD231899.6+3 SD142324252526
8LC16825Within +/- 1 SD13222326
9PP152937Within +/- 1 SD1220-2122242425
10USP201999.9+3 SD1118-19212325
11RC  1016-1719-2021-222324
12SW  914-1517-1819-20222324
13WD  811-1315-1617-1820-2122
14SA  79-1012-1415-1618-192123
15SR  66-89-1112-1415-1719-2021-22
1654-56-89-1112-1416-1819-20
17Subtest TotalPercentileSD41-34-56-89-1114-1516-18
18Core Language45<0.1-3 SD302-34-57-811-1314-15
19Receptive154>99.9+3 SD2135-68-1011-13
20Expressive12192+1 SD100-20-40-70-10
Calculations
Cell Formulas
RangeFormula
G2:G15G2=IFERROR(INDEX(Stanine[Percentile],MATCH(F2,Stanine[Score],0)),"")
H2:H15H2=IFERROR((IF(F2="","",INDEX(Subtest_SD[Distance],MATCH(F2,Subtest_SD[Lookup],-1)))),"")
J2J2=INDEX(SC[Score],MATCH(C2,SC[#Headers],0),MATCH(E2,SC[[5.0]:[8.0]],0))
J3J3=INDEX(SC_long[Score],MATCH(C2,SC_long[#Headers],0),MATCH(E2,SC_long[[5.0]:[8.0]],0))
F18F18=F2+F3+F4+F5
G18:G20G18=IFERROR(INDEX(Stanine[Percentile],MATCH(F18,Stanine[CI_Score],0)),"")
H18:H20H18=IFERROR((IF(F18="","",INDEX(CI_SD[Distance],MATCH(F18,CI_SD[Lookup],-1)))),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S2:S20Cellcontains a blank value textNO
R2:R20Cellcontains a blank value textNO
Q2:Q20Cellcontains a blank value textNO
P2:P20Cellcontains a blank value textNO
O2:O20Cellcontains a blank value textNO
N2:N20Cellcontains a blank value textNO
Cells with Data Validation
CellAllowCriteria
C2List5.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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=SUMIFS(SC[Score],INDEX(SC[[5.0]:[8.0]],,MATCH(C2,SC[[#Headers],[5.0]:[8.0]]+0,0)),E2&"")
 
Last edited:
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It already handles that
Sample_Scoring_File.xlsx
ABCDEFG
1Score5.05.66.06.67.08.0
219
31826
41726
516252626
6152425
7142324252526
813222326
91220-2122242425
101118-19212325
111016-1719-2021-222324
12914-1517-1819-20222324
13811-1315-1617-1820-2122
1479-1012-1415-1618-192123
1566-89-1112-1415-1719-2021-22
1654-56-89-1112-1416-1819-20
1741-34-56-89-1114-1516-18
18302-34-57-811-1314-15
192135-68-1011-13
20100-20-40-70-10
SC
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:G20Cellcontains a blank value textNO
F2:F20Cellcontains a blank value textNO
E2:E20Cellcontains a blank value textNO
D2:D20Cellcontains a blank value textNO
C2:C20Cellcontains a blank value textNO
B2:B20Cellcontains a blank value textNO


Sample_Scoring_File.xlsx
ABCDEFGHIJ
1AgeSubTestRaw ScoreStandard ScorePercentileSD
25.0SC20-211595+2 SD12
3Subtest Scaled ScoreWS261384+1 SD
4FS121163+1 SD
5RS669-1 SD
6WC8825Within +/- 1 SD
7FD231899.6+3 SD
8LC16825Within +/- 1 SD
9PP152937Within +/- 1 SD
10USP201999.9+3 SD
11RC  
12SW  
13WD  
14SA  
15SR  
16
Calculations
Cell Formulas
RangeFormula
G2:G15G2=IFERROR(INDEX(Stanine[Percentile],MATCH(F2,Stanine[Score],0)),"")
H2:H15H2=IFERROR((IF(F2="","",INDEX(Subtest_SD[Distance],MATCH(F2,Subtest_SD[Lookup],-1)))),"")
J2J2=SUMIFS(SC[Score],INDEX(SC[[5.0]:[8.0]],,MATCH(C2,SC[[#Headers],[5.0]:[8.0]]+0,0)),E2&"")
Cells with Data Validation
CellAllowCriteria
C2List5.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

New Member
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Perfect. A clear and direct answer. Thank you for everything, Fluff.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,757
Members
418,411
Latest member
Excellency

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top