Index Match Question - What am I doing wrong?

**JackieAdams** · 07-24-2012, 06:59 PM

I teach adult education classes (commonly known as GED classes). When students come in they take two math tests (one with Computation and one Applied Mathematics). Those two test scores must be combined to arrive at a combined scale score and grade level. To complicate matters, there are 4 different test versions (A9, A10, D9, D10). So what I need Excel to do is to:

1. Determine the appropriate form (a9, A10, D9, or d10)
2. Based on that form, find the number correct on the Computation part, and the number correct on the Applied Mathematics part
3. When all three of those match I need it to give me the scale score and educational level that corresponds. There is a spot for both of these on the data sheet

I am attaching the workbook and hopefully you can see what I am talking about. I know this probably sounds strange.

The problem I have found (and basically I just googled this and found this Index thing so I really have no clue what I am doing): When the number correct on the computation is smaller than the number correct on the Applied Mathematics part, the thing seems to work. BUT when the computation number is larger than the applied number it returns incorrect values. So I am clueless as to how to fix this and hoping someone: A. Understands what the heck I am trying to do and B. Can help me with an easy fix. No laughing at my efforts please lol.

**JosephP** · 07-24-2012, 07:31 PM

try these:
in E24: =LOOKUP(2,1/(('Math LookUps'!$A$344:$A$8685=B19)*('Math LookUps'!$B$344:$B$8685=C19)*('Math LookUps'!$C$344:$C$8685=C24)),'Math LookUps'!$D$344:$D$8685)
in E26: =LOOKUP(2,1/(('Math LookUps'!$A$344:$A$8685=B19)*('Math LookUps'!$B$344:$B$8685=C19)*('Math LookUps'!$C$344:$C$8685=C24)),'Math LookUps'!$E$344:$E$8685)

**JackieAdams** · 07-27-2012, 01:56 PM

What a happy day. That seems to be working very good. Thanks sooooo much. I hate having to look all those numbers up on paper charts. No more. One request if you would be so kind. Can you explain that formula to me? I think I understand parts of it but not all such as the * between the different things and the 2,1/. I don't generally have to do such complicated tasks.

**JosephP** · 07-27-2012, 02:25 PM

('Math LookUps'!$A$344:$A$8685=B19)
creates an array of TRUE or FALSE values as do the other two similar sections. multiplying these together treats TRUE as 1 and FALSE as 0 so you get one array containing 1 where all the criteria are true (1*1*1) and 0 if any criteria are not met. dividing 1 by this array creates a further array of either 1 (1 / 1) or an error value (1/0).
LOOKUP ignores error values and tries to look up 2 in an array of 1s and errors. since 2 is not found it matches the highest value less than that, which is 1. it then returns the relevant value from column D or E as applicable.
simple, huh? ;-)

**JackieAdams** · 07-27-2012, 03:32 PM

Oh, yeah, so simple I am still dizzy. Perhaps this is how my students feel when I try to explain algebra to some of them. Obviously you use this stuff quite a bit. Very impressive! Thanks again. I will first try to figure out more about that array stuff (LOL). I keep seeing that coming up.

**Cutter** · 07-27-2012, 04:43 PM

@ JackieAdams

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.