Excel INDEX/MATCH question

Factual Questions
1

I was having a problem which I posted in the Ask the Excel Guy 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).

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” function code (default value=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):

Here is the code (cell E7 - you have to hold ctrl-shift when you hit return to insert {} around the function):

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?

2

Sorry, but I’m somewhat confused as to what you’re trying to do.

3

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:

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.

4

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?

5

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.

  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.



        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+
6

Yes, but if the range is restricted, then why not just break it out into the individual numbers?

7

Lots of typing? :wink:

8

Well, I have something that works here, but man it’s ugly!



	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)
9

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).

10

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!

11

You are most welcome, glad I could help!