I need to get the formula to add 2 columns together for 2 letters for the letters B and P

[zotah]

I need to get the formula to add 2 columns together for 2 letters for the letters B and P
Then I need them to be transcribed using the reference table so the 4 combination of BB BP PP and PB can each be turned in to a reverenced real number.

I was hoping I could do it with using the concatenate formula
So here's my problem

Reverence Table
BB 1
PP 1
BP -1
PB -1

My excel layout is like this
Column A ------- Column B ------- Column C ------- Column D
---B-------------Blank---------------P-------(Trying To Get Formula?)

I need to get formula for Column D.

So I see Column A and Column C and I want the B and the P to concatenate together and then have lookup figure out from the reverence table that B and P together add up to -1 and have -1 go into Column “D” as the answer.
I then want to “lock it” “F4” so I can drag it down a long row downward on excel so it figures each and every cell below it so it will calculate each and every one of the 4 posible answers of “BB” “PP” “PB” and “PB” and always references the correct answer in the formula. So Column D is all the way down the cells reverences many cells downward.
Can you help me with the formula?
Thanks

 

[zotah]

This is all I get now,

[TABLE="width: 327"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[zotah]

At least the N/A are gone.

Getting closer to the formula

Any other suggestions?

Gary

 

[zotah]

Why is this formula not picking up the other 3 variations of PP BB and PB?

What needs to be changed or added to this formula


=IFERROR(VLOOKUP(B10&D10,$A$3:$B$6,2,0),"")



Gary

 

[mole999]

needs to be set correctly but this looks at B and D whilst testing in H

=IFERROR(LOOKUP(B2&D2,{"BB","BP","PP","PB"},{1,-1,1,-1}),"")

actually this is far shorter but follows the same logic

=IF(B2=D2,1,-1)

 

[zotah]

Thanks but, It's still not computing the formula correctly. I get answers in every square not but if you look at answers on each cell you'll see clearly that the answers are mostly wrong.

Look at the table array and see that that the first set of letters are B B and they are not -1 (B) and (B) should be giving an answer of 1 and look at the
number 6 (P) and (B) are supposed to give an answer of 1 and it's retuning the answer of -1

Any other idea's on how I can get the formula to work I'd sure appreciate it.
Heres the table array again

[TABLE="width: 86"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]PB[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PP[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="width: 327"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]P[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

place this in D10

=IF(TRIM(A10)=TRIM(C10),1,-1)

drag up and down to fill the column as required

basically I am saying if left and right are equal then you get a 1, otherwise its considered not equal or bits missing (in this case two empty cell will provide 1) -1. Trim is to try an eliminate any letter with a space which would make it unequal

 

[zotah]

IT WORKS!!!!!

Thanks so much, I've been working on this for a week.

Your terrrific,

Gary