Hello and I could use some help

[slvrphoenix4]

Well I'm new to this board. I'm having trouble using and index formula not updating every time 1 of 2 cells change. I want it to take the number I put in cell h2 and compare it to cell z5(which as a drop down choice of terrible, poor, average, good, excellent. I'm wanting the formula to reference the cell were they would intersect for my answer. The problem I'm having is that it will only fill in the number that I put in h2 regardless of what I choose out of the list box in z5. Thank you very much. Any help would be very appreciated^^v

My formula is :
=INDEX(info!$P$2:$Z$22,MATCH(cover!H2,info!$P$2:$P$22,1),MATCH(cover!Z5,info!$P$2:$Z$2,1))

the table I'm referencing is:


Terrible Poor Average Good Excellent
1 0 0 1 1 1
2 0 1 1 2 2
3 1 1 2 2 3
4 1 2 2 3 4
5 1 2 3 4 5
6 1 2 4 5 6
7 1 3 4 6 7
8 2 3 5 6 8
9 2 4 5 7 9
10 2 4 7 8 10
11 2 4 7 9 11
12 2 5 8 10 12
13 3 5 8 10 13
14 3 6 9 11 14
15 3 6 9 12 15
16 3 6 10 13 16
17 3 7 10 14 17
18 4 7 11 14 18
19 4 8 11 15 19
20 4 8 12 16 20

 

[slvrphoenix4]

sorry the table didn't come over very well

there is supposed to be a column of number underneath all of the words except for the number on the far right. Which will have no header. Sorry about that. >.>

 

[Peter_SSs]

Welcome to the MrExcel board!

To post better screen shots, look at the 3 methods suggested in my signature block. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

I haven't replicated your set-up since it apparently spans 2 worksheets, but try making the red change.

=INDEX(info!$P$2:$Z$22,MATCH(cover!H2,info!$P$2:$P$22,1),MATCH(cover!Z5,info!$P$2:$Z$2,0))


What worksheet is the formula on?

 

[slvrphoenix4]

Thank you very much. It worked. Why did it work by changing the 1 to a 0? Most of what I know about excel I have learned on my own and trial and error. Thank you again.

 

[Peter_SSs]

Why did it work by changing the 1 to a 0?

The '1' is generally used to find an approximate match and the data needs to be arranged in ascending order. Your headings across the top are not in ascending order. Changing the final argument to '0' means the MATCH is required to find an exact match and the items do not need to be in order. Look in the built-in Help on the MATCH function for more information.


What about:

What worksheet is the formula on?