# Excel INDEX/MATCH question
**URL:** https://boards.straightdope.com/t/excel-index-match-question/439714
**Category:** Factual Questions
**Created:** [March 1, 2008, 4:32am UTC](https://boards.straightdope.com/t/excel-index-match-question/439714 "2008-03-01T04:32:47Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@phungi](https://boards.straightdope.com/u/phungi)
#### Post date: [March 1, 2008, 4:32am UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/1 "2008-03-01T04:32:47Z")
I was having a problem which I posted in the [Ask the Excel Guy](http://boards.straightdope.com/sdmb/showthread.php?t=398567) thread in MPSIMS, but the problem changed and that thread doesn’t appear to get much traffic (last posted in 10/07).
So, in trying to reference a table of normative data, I arrived at the following solution that allows you to query a cell range based on two independent cells that each query separate columns (code I based the solution on is [here](http://www.contextures.com/xlFunctions03.html#IndexMatch4)).
My data is entered into the norm table as “lowest” values, such that “18” represents “18-20”, and this appears to jive with the “[MATCH](http://www.techonthenet.com/excel/formulas/match.php)” function code (default value=1).
> [@](#):
>
> The Match function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order.
>
> If the match\_type parameter is omitted, the Match function assumes a match\_type of 1.
However, while the code in the example works perfect for exact values (e.g., “18” and “55” returns “1”) it does not return the proper number when a non-exact reference value is used (e.g., “19” or “56”).
I spent a lot of time just getting to this solution, so I am hoping to tweak it rather than tear it apart and start over, so any help would be appreciated.
Here is the Table (A3:A29):
> [@](#):
>
> 03 50 0
> 03 60 1
> 03 68 2
> 03 80 3
> 06 50 0
> 06 57 1
> 06 64 2
> 06 71 3
> 09 50 0
> 09 57 1
> 09 60 2
> 09 64 3
> 12 50 0
> 12 56 1
> 12 59 2
> 12 64 3
> 15 50 0
> 15 55 1
> 15 59 2
> 15 64 3
> 18 50 0
> 18 55 1
> 18 58 2
> 18 63 3
> 21 50 0
> 21 55 1
> 21 58 2
> 21 61 3
Here is the code (cell E7 - you have to hold ctrl-shift when you hit return to insert {} around the function):
> [@](#):
>
> =INDEX($C$3:$C$29,MATCH(1,(D7=$A$3:$A$29)\*(D8=$B$3:$B$29),0))
Type: “18” (in cell D7) and “55” (in cell D8).
I think the problem lies with the fact that my data are not sorted in ascending order, but I am not sure how to achieve this with the two columns. Any ideas?
---
### Author:  [@Quartz](https://boards.straightdope.com/u/Quartz)
#### Post date: [March 1, 2008, 9:55am UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/2 "2008-03-01T09:55:45Z")
Sorry, but I’m somewhat confused as to what you’re trying to do.
---
### Author:  [@phungi](https://boards.straightdope.com/u/phungi)
#### Post date: [March 1, 2008, 1:20pm UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/3 "2008-03-01T13:20:28Z")
I’m trying to automate a calculation that uses a table of normative data. Certain combinations of scores return a “0” (normal), others a “1” (mild), etc. The table is arranged like this:
> [@](#):
>
> ---------0------1-------2------3
> 21-99 50-54 55-57 58-60 61+
> 18-20 50-54 55-57 58-62 63+
> 15-17 50-54 55-58 59-63 64+
So, if the total score on variable #1 is 18, you use row 2, and reference variable #2. If that is 55, you return a “1”. (ignore the dashes, I used them to space the first row properly).
Let me know if that makes sense.
---
### Author:  [@Quartz](https://boards.straightdope.com/u/Quartz)
#### Post date: [March 1, 2008, 1:44pm UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/4 "2008-03-01T13:44:05Z")
I think I see. Aren’t you being needlessly complex? Why don’t you simply split it all out so you’ve got each of values for each of the two variables along the two axes?
---
### Author:  [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)
#### Post date: [March 1, 2008, 2:57pm UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/5 "2008-03-01T14:57:03Z")
[QUOTE=Quartz]
I think I see. Aren’t you being needlessly complex? Why don’t you simply split it all out so you’ve got each of values for each of the two variables along the two axes?
[/QUOTE]
I think I see why. \*\*phungi \*\* is trying to do a lookup on two numbers, determining which \*range \* each of the numbers fall in without having to put every number in every range in the table, just the highest number in each range.
\*\*phungi \*\* is correct that given the nature of this data you can’t have both columns sorted in strict ascending order for this particular problem.
I am going to give this some thought but in the meantime I have two suggestions.
1. Not to disparage the Excel guy, but the best advice on Excel to be found anywhere is on [www.ozgrid.com](http://www.ozgrid.com).
2. Use CODE tags instead of QUOTE tags to show your data. It will use a fixed-width font and it’s much easier to get the spacing you want. Write your data in a fixed-width editor or using Courier font in Word and then paste it in.
```auto
0 1 2 3
21-99 50-54 55-57 58-60 61+
18-20 50-54 55-57 58-62 63+
15-17 50-54 55-58 59-63 64+
```
---
### Author:  [@Quartz](https://boards.straightdope.com/u/Quartz)
#### Post date: [March 1, 2008, 3:32pm UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/6 "2008-03-01T15:32:33Z")
Yes, but if the range is restricted, then why not just break it out into the individual numbers?
---
### Author:  [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)
#### Post date: [March 1, 2008, 5:02pm UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/7 "2008-03-01T17:02:32Z")
[QUOTE=Quartz]
Yes, but if the range is restricted, then why not just break it out into the individual numbers?
[/QUOTE]
Lots of typing? 😉
---
### Author:  [@K364](https://boards.straightdope.com/u/K364)
#### Post date: [March 1, 2008, 6:33pm UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/8 "2008-03-01T18:33:46Z")
Well, I have something that works here, but man it’s ugly!
```auto
A B C D E
1 0 1 2 3
2 15 50 55 59 64
3 18 50 55 58 63
4 21 50 55 58 61
5
6
7 Score1: 17
8 Score2: 58
Row of Score1: 1 =MATCH(B7,A2:A4,1)
Addr of Row: $B$2:$E$2 =ADDRESS(B10+1,2) & ":" & ADDRESS(B10+1,5)
Col of Score2: 2 =MATCH(B8,INDIRECT(B11),1)
Final Answer: 1 =OFFSET(A1,,B12)
```
---
### Author:  [@phungi](https://boards.straightdope.com/u/phungi)
#### Post date: [March 2, 2008, 3:26am UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/9 "2008-03-02T03:26:24Z")
Thanks for the input. This is an example of one of several normative tables that I will be referencing, so typing out every option from every table is not feasible.
**K364** thanks for the solution, which looks quite elegant to me! I will try it out when I get home (out of town for the weekend).
---
### Author:  [@phungi](https://boards.straightdope.com/u/phungi)
#### Post date: [March 3, 2008, 4:42am UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/10 "2008-03-03T04:42:34Z")
Thanks, **K364**! I had to tweak a few numbers, but works like a charm. I was able to use this to reference several normative data tables, without having to type more than a few numbers/columns. Much appreciated!
---
### Author:  [@K364](https://boards.straightdope.com/u/K364)
#### Post date: [March 3, 2008, 5:33am UTC](https://boards.straightdope.com/t/excel-index-match-question/439714/11 "2008-03-03T05:33:45Z")
You are most welcome, glad I could help!