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

 

[mole999]

=d5&e5
=if(f5="bp",-1,"")

 

[zotah]

I'll try it

Thanks

 

[ZAX]

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

Hi,
In D2 use the following and auto fill down:

=VLOOKUP(A2&C2,$E$2:$F$5,2,FALSE)

Note: $E$2:$F$5 is the address of the reference table as I proposed, U can change it to fit ur table.
ZAX

 

[zotah]

Thanks
I'll try that

Gary

 

[zotah]

Ok I tried the formula listed about with all the correct cell information and here is what I got

[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]#N/A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]#N/A[/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]#N/A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]#N/A[/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]#N/A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]#N/A[/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]#N/A[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]#N/A[/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]
</tbody>[/TABLE]

 

[zotah]

It seems like B and P are grabbing the answer from my "table array" but the rest are getting NA answers down the list.

Here is the exact formula I'm using

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

ans here is my "table array"

[TABLE="width: 86"]
<colgroup><col span="2"></colgroup><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]BP [/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

 

[zotah]

Can you help me figure out what I'm doing wrong in my formula so far

Thanks so much

Gary

6-7-8 are all part of the same one question

 

[mole999]

does this do it

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

 

[zotah]

I'll let you know

Thanks