Vlookup with multiple conditions

[hiboumel]

Hi, I'm a bit stumped, any assistance would be appreciated. I'm trying to return the values "Hard", "Instructional" or "Independent" dependent upon a number of conditions from a student's reading test.

The formula needs to first determine whether they completed a task from A to K (alpha numeric value of 1 to 11) or from L to Z (alpha numeric value of 12 to 26). The value to be returned must then depend on a combination of their 'Comprehension' as well as their 'Accuracy' scores. The values in columns E to G will be input by the user.

Sorry, I couldn't figure out how to use the 'index' and 'match' functions in this case (they are data range points rather than a set column with a given value?).

Re: the screenshots below - first is the data output table with sample values provided (the yellow cell is where the value of 'hard', 'inst' or 'ind' would be returned). The second screenshot is the data table providing the conditions and value ranges.

Thanks,
Mel

[/IMG]

 

[skywriter]

Using the example data provided, how would you describe the process you would use to manually determine you desired outcome. In other words describe in plain English how you would use the data on the line with the name Mel to look in the example at the bottom to come up with the answer of either Hard, Instructional or Independent.
It's not clear to me how you use the data on top to look at the data below to come up with an answer.

 

[hiboumel]

Hi! Sorry, to clarify:
Eg.1 Mel completes a test/task at Level D (or Level 4). The student scores 91% for accuracy and a comprehension score of 2. In manual terms, I would look up the table for books levelled A-K (aka Level 1 to 11), and determine that with 91% accuracy and 2/7 comprehension, that the task was 'Hard'.
Eg.2 - Student B completes a test/task at level M (Level 13). I would then refer to the L-Z table (aka Level 12-26). They score 7/10 for comprehension and 96% for accuracy, which would result in that task being 'Instructional'.

These are the original benchmarks I'm working from for assessing the data.

 

[skywriter]

You will have to adapt this to your actual range references, but just study carefully what I am referencing in my example data and you should be able to manage it.

Sheet9

*	A	B	C	D	E	F	G
HARD
INSTRUCTIONAL
INDEPENDENT
HARD
INSTRUCTIONAL
HARD
HARD
INSTRUCTIONAL
INDEPENDENT
HARD
INSTRUCTIONAL
HARD
*	*	*	*	*	*	*
*	*	*	*	*	*	*
Mel	HARD	*
Cindy	INDEPENDENT	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:122.67px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:104.67px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3.4[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]4.5[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]4.6[/TD]
[TD="align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]94.9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4.5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]94.9[/TD]
[TD="align: center"]4.6[/TD]
[TD="align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4.9[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6.9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]97[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]6.9[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]97[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]94.9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]8[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B16 =INDEX($G$2:$G$13,(SUMPRODUCT((C16>=$A$2:$A$13)*(E16>=$C$2:$C$13)*(E16<=$D$2:$D$13)*(F16>=$E$2:$E$13)*(F16<=$F$2:$F$13)*(ROW($G$2:$G$13)-ROW($G$2)+1)))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[hiboumel]

Thanks Bruce! It's worked for my first row but not the others. I'll double check the cell references are updating correctly. Thanks again.

 

[hiboumel]

Hi, I'm so sorry if this is getting painful. It doesn't seem to be working for me. I checked the formula a number of times, but can't see where it is different from yours (after updating cell references).
Attached below is a screen shot of the 2 worksheets and the formula entered for 1 of the cells that isn't working.
Thanks (and sorry),
Mel

 

[skywriter]

The problem may lie with the numbers that are formatted as %, I was thinking of mentioning this to you before I posted my original solution, but it slipped my mind, I apologize.

In your top sheet you have numbers such as 91% and in the bottom they just appear to be integers. These numbers would have to be equally formatted for the formula to work. If they are different then 91% is technically the number 0.91 not the same as 91.

 

[hiboumel]

Thanks Bruce. That worked perfectly with the 1-11 (or A-K) levels, but I'm still getting a reference error with the higher levels. Any ideas? Have I missed something obvious?

 

[Aladin Akyurek]

Posing images are not helpful for these cannot be read into Excel.

 

[hiboumel]

Hi, sorry - thought it would help to see which cell wasn't working and the correlating data. Here is the formula in C15 that is returning the #REF error.


=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D15>='data tables'!$A$2:$A$13)*(F15>='data tables'!$C$2:$C$13)*(F15<='data tables'!$D$2:$D$13)*(G15>='data tables'!$E$2:$E$13)*(G15<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))