# 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
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)))),"")
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=SUMIFS(SC[Score],INDEX(SC[[5.0]:[8.0]],,MATCH(C2,SC[[#Headers],[5.0]:[8.0]]+0,0)),E2&"")``

Last edited:

#### Fluff

##### MrExcel MVP, Moderator
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.

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
Hi & welcome to MrExcel.
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

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)))),"")
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
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

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
Perfect. A clear and direct answer. Thank you for everything, Fluff.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
6
Views
7K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,127
Messages
5,768,286
Members
425,460
Latest member
Astros1243

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