Index formula

[Preacherman771]

I have an INDEX formula which I entered in Q28 and then clicked on the + and dragged down to Q31. It works in the first cell but returns "n/a" in the rest of the column. I even manually entered the formula in each of the cells and still received the same result "n/a". Any help in understanding why this is occurring would be greatly appreciated.

Q28 =INDEX($M$56:$M$59,MATCH(1,(M28=$L$56:$L$59)*(R28=$L$56:$L$59),0)) Returned "R1L1a"
Q29 =INDEX($M$56:$M$59,MATCH(1,(M29=$L$56:$L$59)*(R29=$L$56:$L$59),0)) Returned "n/a"
Q30 =INDEX($M$56:$M$59,MATCH(1,(M30=$L$56:$L$59)*(R30=$L$56:$L$59),0)) Returned "n/a"
Q31 =INDEX($M$56:$M$59,MATCH(1,(M31=$L$56:$L$59)*(R31=$L$56:$L$59),0)) Returned "n/a"

M28 R1L0a N28 1 R28 1 L56 R1L0a M56 R1L1a
M29 R1L0b N29 2 R29 3 L57 R1L0b M57 R1L0b
M30 R1L0c N30 3 R30 2 L58 R1L0c M58 R1L0c
M31 R1L0d N31 4 R31 4 L59 R1L0d M59 R1L0d

 

[Preacherman771]

I have discovered another component. When the ranking in N28:N31 is 1,2,3,4, the index results are accurate. However if the order of 1 thru 4 changes so does the results between accurate and "n/a".

N28 - 1 Q28 - R1L1a N28 - 2 Q28 - "n/a" N28 - 3 - "n/a" N28 - 4 - "n/a"
N29 - 3 Q29 - "n/a" N29 - 4 Q29 - "n/a" N29 - 2 - R1L1b N29 - 1 - "n/a"
N30 - 4 Q30 - "n/a" N30 - 3 Q30 - R1L1c N30 - 1 - "n/a" N30 - 2 - "n/a"
N31 - 2 Q31 - "n/a" N31 - 1 Q31 - "n/a" N31 - 4 - R1L1d N31 - 3 - "n/a"

 

[Alex Blakenburg]

What am I missing here ?
If R = 1,2,3,4 as in.
M29 R1L0b N29 2 R29 3 L57 R1L0b M57 R1L0b

Then the following is never going to be TRUE
(R29=$L$56:$L$59)

 

[Preacherman771]

Your scenario and question got me rethinking and looking at my formula and agreeing it would never return the result I wanted. After rethinking the process I came up with the following formula "=INDEX($M$56:$M$59,MATCH((INDEX($M$28:$M$31,MATCH(R28,N$28:N$31,0))),$L$56:$L$59,0))" in cell Q28 and then clicked and dragged down to Q31. After testing different values in column R28:R31, all of the results came out accurately.

Thank you so much for helping me see my error, thus getting me to rethink the process.

 

[Alex Blakenburg]

Thank you for the feedback. Glad I could help.