How to correct formula

[Jordans121]

I have a problem ending this formula correctly. Can anyone find the error in this formula. L11 has the formula below in it. labeling Example.xls
=(N(NOT(ISNA(MATCH(F11,$U$4:$U$9,0))))*0+N(NOT(ISNA(MATCH(F11,$V$4:$V$9,0))))*1+N(NOT(ISNA(MATCH(F11,$W$4:$W$9,0))))*2+N(NOT(ISNA(MATCH(F11,$X$4:$X$9,0))))*3+N(NOT(ISNA(MATCH(F11,$Y$4:$Y$9,0))))*4+N(NOT(ISNA(MATCH(F11,$Z$4:$Z$9))))*5+N(NOT(ISNA(MATCH(F11,$AA$4:$AA$9,0))))*6+N(NOT(ISNA(MATCH(F11,$AB$4:$AB$9,0))))*7+N(NOT(ISNA(MATCH(F11,$AC$4:$AC$9,0))))*8+N(NOT(ISNA(MATCH(F11,$AD$4:$AD$9,0))))*9+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$U$4:$U$9,0))))*0+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$V$4:$V$9,0))))*1+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$W$4:$W$9,0))))*2+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$X$4:$X$9,0))))*3+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$Y$4:$Y$9,0))))*4+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$Z$4:$Z$9,0))))*5+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$AA$4:$AA$9,0))))*6+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$AB$4:$AB$9,0))))*7+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$AC$4:$AC$9,0))))*8+N(NOT(ISNA(MATCH(RIGHT(F11,1)&LEFT(F11,1),$AD$4:$AD$9,0))))*9+N/IF(LEFT(F11,1)=RIGHT(F11,1),2,1))

[daddylonglegs]

Are you getting an actual error or just the wrong result? What result do you expect?

Seems to me you can probably use a much shorter formula...but I don't understand the purpose of using RIGHT(F11,1)&LEFT(F11,1), isn't that just the same as F11 when F11 has 2 characters.....or are you expecting a different number of characters?

[Jordans121]

I'm getting the wrong result for L11. Back pair 07 (in H11) can be found in the column (AB) labeled 7 so the result should be 7.

[Jordans121]

I do not understand what you mean...I'm simply trying to match the pairs in columns F,G,H to the corresponding chart shown in columns U through AD and return the number (with the blue background) on the first row of columns U through AD. The formula would be placed in columns J,K,and L.

I suppose a =VLOOKUP formula can be used but I do not know how to use it.

[mike65535]

No clue what you are trying to do here.

Noticed that the formula in L11 does not look like the formulae above and below in that it used F11 instead of H11 (H11 would appear to fit the pattern)

[mike65535]

I'm simply trying to match the pairs in columns F,G,H to the corresponding chart

The pairs? They look like numbers to me - can you not treat them as such? Will they always be two digits?

[Jordans121]

You are correct. I changed F11 to H11 but it is still not returning the correct value. if you look at L11 you will see the number 12 instead of the number 7. if you look at H11 and match its cell contents to the chart (columns U:AD) you will see that07 should be labeled 7.

There is only one number (a number 0-9)that will show up for the formula. Pairs are always two digits. I don't understand what you are asking.

Note:I want use the numbers at the top of the chart as "labels" or "titles". PLEASE SEE THE ATTACHMENT.labeling Example.xls

[mike65535]

You go thru great pains to treat the left and right side of the pair separately. Why? What is your goal?

I don't understand what you are asking.

I don't think you've educated us enough to offer any useful advice. Why not take us thru the algorithm that you want to occur?

[Jordans121]

I did not do this formula. someone else did it for me. there is no reason to separate the left from the right. I just want a formula to match and return.

[daddylonglegs]

The reason you are getting the wrong result is because the MATCH function for *5 is missing the 3rd argument, should be like this

N(NOT(ISNA(MATCH(H11,$Z$4:$Z$9,0))))*5

but for a shorter formula you can use this to replace the whole thing

=SUMPRODUCT(($U$4:$AD$9=H11)*$U$1:$AD$1)

[Jordans121]

Thanks alot daddylonglegs. +Rep