Vlookup with multiple conditions

H

hiboumel

New Member
Joined
Jan 4, 2015
Messages
8
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

f0qgp0.png
[/IMG]
 
Sort by date Sort by votes
hiboumel said:
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))))
Click to expand...


Use this program to post both of your sheets onto this board. These are what Mr. Akyurek is referring to. If you do that we can copy and paste them into Excel and it would be much much easier to help you. I created the data in my original post by hand, but it's not going to help here.
Excel tables to the web Excel Jeanie Html
 
Last edited:
Upvote 0
Ahh, sorry I understand. I'm on a Mac, so have installed from a virtual PC environment and copied it over. Hopefully this helps?

TEST

*ABCDEFG
*******
**
**
**
*******
**
**
**
**
*******
**#REF!
**#REF!
**#REF!
**

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:39px;"><col style="width:91px;"><col style="width:139px;"><col style="width:64px;"><col style="width:60px;"><col style="width:60px;"><col style="width:63px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Date[/TD]
[TD="bgcolor: #ffff00, align: center"]Hard/Inst/ Indep.[/TD]
[TD="align: center"]Alpha-numeric level value[/TD]
[TD="align: center"]Reading Level[/TD]
[TD="align: center"]Acc %[/TD]
[TD="align: center"]Total Compr'n[/TD]

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

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Mel[/TD]
[TD="align: center"]05/02/15[/TD]
[TD="align: center"]HARD[/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"]6[/TD]

[TD="align: center"]INSTRUCTIONAL[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]94[/TD]
[TD="align: center"]5[/TD]

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

[TD="align: center"]INSTRUCTIONAL[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]4[/TD]

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

[TD="align: center"]INDEPENDENT[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]6[/TD]

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

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

[TD="align: center"]HARD[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]2[/TD]

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

[TD="align: center"]INSTRUCTIONAL[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]94[/TD]
[TD="align: center"]5[/TD]

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

[TD="align: center"]INSTRUCTIONAL[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]4[/TD]

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

[TD="align: center"]INDEPENDENT[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]6[/TD]

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

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

[TD="align: center"]13[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]3[/TD]

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

[TD="align: center"]13[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]94[/TD]
[TD="align: center"]5[/TD]

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

[TD="align: center"]13[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]7[/TD]

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

[TD="align: center"]HARD[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]7[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
C5=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D5>='data tables'!$A$2:$A$13)*(F5>='data tables'!$C$2:$C$13)*(F5<='data tables'!$D$2:$D$13)*(G5>='data tables'!$E$2:$E$13)*(G5<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D5=IF(ISERROR(VLOOKUP(E5,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E5,'data tables'!$J$3:$K$28,2,FALSE))
C6=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D6>='data tables'!$A$2:$A$13)*(F6>='data tables'!$C$2:$C$13)*(F6<='data tables'!$D$2:$D$13)*(G6>='data tables'!$E$2:$E$13)*(G6<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D6=IF(ISERROR(VLOOKUP(E6,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E6,'data tables'!$J$3:$K$28,2,FALSE))
C7=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D7>='data tables'!$A$2:$A$13)*(F7>='data tables'!$C$2:$C$13)*(F7<='data tables'!$D$2:$D$13)*(G7>='data tables'!$E$2:$E$13)*(G7<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D7=IF(ISERROR(VLOOKUP(E7,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E7,'data tables'!$J$3:$K$28,2,FALSE))
C8=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D8>='data tables'!$A$2:$A$13)*(F8>='data tables'!$C$2:$C$13)*(F8<='data tables'!$D$2:$D$13)*(G8>='data tables'!$E$2:$E$13)*(G8<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D8=IF(ISERROR(VLOOKUP(E8,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E8,'data tables'!$J$3:$K$28,2,FALSE))
D9=IF(ISERROR(VLOOKUP(E9,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E9,'data tables'!$J$3:$K$28,2,FALSE))
C10=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D10>='data tables'!$A$2:$A$13)*(F10>='data tables'!$C$2:$C$13)*(F10<='data tables'!$D$2:$D$13)*(G10>='data tables'!$E$2:$E$13)*(G10<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D10=IF(ISERROR(VLOOKUP(E10,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E10,'data tables'!$J$3:$K$28,2,FALSE))
C11=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D11>='data tables'!$A$2:$A$13)*(F11>='data tables'!$C$2:$C$13)*(F11<='data tables'!$D$2:$D$13)*(G11>='data tables'!$E$2:$E$13)*(G11<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D11=IF(ISERROR(VLOOKUP(E11,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E11,'data tables'!$J$3:$K$28,2,FALSE))
C12=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D12>='data tables'!$A$2:$A$13)*(F12>='data tables'!$C$2:$C$13)*(F12<='data tables'!$D$2:$D$13)*(G12>='data tables'!$E$2:$E$13)*(G12<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D12=IF(ISERROR(VLOOKUP(E12,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E12,'data tables'!$J$3:$K$28,2,FALSE))
C13=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D13>='data tables'!$A$2:$A$13)*(F13>='data tables'!$C$2:$C$13)*(F13<='data tables'!$D$2:$D$13)*(G13>='data tables'!$E$2:$E$13)*(G13<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D13=IF(ISERROR(VLOOKUP(E13,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E13,'data tables'!$J$3:$K$28,2,FALSE))
D14=IF(ISERROR(VLOOKUP(E14,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E14,'data tables'!$J$3:$K$28,2,FALSE))
C15=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))))
D15=IF(ISERROR(VLOOKUP(E15,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E15,'data tables'!$J$3:$K$28,2,FALSE))
C16=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D16>='data tables'!$A$2:$A$13)*(F16>='data tables'!$C$2:$C$13)*(F16<='data tables'!$D$2:$D$13)*(G16>='data tables'!$E$2:$E$13)*(G16<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D16=IF(ISERROR(VLOOKUP(E16,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E16,'data tables'!$J$3:$K$28,2,FALSE))
C17=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D17>='data tables'!$A$2:$A$13)*(F17>='data tables'!$C$2:$C$13)*(F17<='data tables'!$D$2:$D$13)*(G17>='data tables'!$E$2:$E$13)*(G17<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D17=IF(ISERROR(VLOOKUP(E17,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E17,'data tables'!$J$3:$K$28,2,FALSE))
C18=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D18>='data tables'!$A$2:$A$13)*(F18>='data tables'!$C$2:$C$13)*(F18<='data tables'!$D$2:$D$13)*(G18>='data tables'!$E$2:$E$13)*(G18<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))
D18=IF(ISERROR(VLOOKUP(E18,'data tables'!$J$3:$K$28,2,FALSE)),"",VLOOKUP(E18,'data tables'!$J$3:$K$28,2,FALSE))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


data tables

*ABCDEFG
HARD
INSTRUCTIONAL
INDEPENDENT
HARD
INSTRUCTIONAL
HARD
HARD
INSTRUCTIONAL
INDEPENDENT
HARD
INSTRUCTIONAL
HARD

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:87px;"><col style="width:87px;"><col style="width:87px;"><col style="width:87px;"><col style="width:88px;"><col style="width:88px;"><col style="width:116px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Task level - Start[/TD]
[TD="align: center"]Task level - End[/TD]
[TD="align: center"]Accuracy start[/TD]
[TD="align: center"]Accuracy end[/TD]
[TD="align: center"]Comp. start[/TD]
[TD="align: center"]Comp. end[/TD]
[TD="align: center"]Achieved output[/TD]

[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: right"]0[/TD]
[TD="align: right"]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: right"]3.5[/TD]
[TD="align: right"]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: right"]4.6[/TD]
[TD="align: right"]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: right"]0[/TD]
[TD="align: right"]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: right"]4.6[/TD]
[TD="align: right"]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: right"]0[/TD]
[TD="align: right"]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: right"]0[/TD]
[TD="align: right"]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: right"]5[/TD]
[TD="align: right"]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: right"]7[/TD]
[TD="align: right"]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: right"]0[/TD]
[TD="align: right"]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: right"]7[/TD]
[TD="align: right"]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: right"]0[/TD]
[TD="align: right"]10[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
I apologize, I made an error in the formula. Put the formula below in C3 and copy down and you should be fine.

Excel 2010
C
HARD

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

</tbody>
Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=INDEX('data tables'!$G$2:$G$13,(SUMPRODUCT((D3>='data tables'!$A$2:$A$13)*(D3<='data tables'!$B$2:$B$13)*(F3>='data tables'!$C$2:$C$13)*(F3<='data tables'!$D$2:$D$13)*(G3>='data tables'!$E$2:$E$13)*(G3<='data tables'!$F$2:$F$13)*(ROW('data tables'!$G$2:$G$13)-ROW('data tables'!$G$2)+1))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Vlookup not working when range is extended.
Replies
1
Views
149
Fluff
Fluff
M
  • Solved
VLOOKUP breaking a subsequent sum formula
Replies
3
Views
142
etaf
etaf
A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
1K
AndyB86
A
A
  • Solved
#REF! error when using INDEX MATCH
Replies
5
Views
624
Fluff
Fluff
B
  • Question Question
Sort/Filter based on SUMIF Value
2
Replies
13
Views
1K
Fluff
Fluff

Share this page

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
Back
Top