Using if statement for excel

Question

I have a 3 data columns written as text and I want to output some text based on their condition using if statement Ex)

If right end letter of column 1 is “a” and left letter of column 2 is “b” the output should be “c” and if left end letter of column 1 is “d” and left letter of column 2 is “e” the output should be “f”

I have tried if statement with AND but somehow the code does not run and I need help!

Answer 1

One option:

Formula in D1:

=CONCAT(XLOOKUP(MID(A1,SEQUENCE(2,,1,LEN(A1)-1),1)&LEFT(B1),{"ab","de"},{"c","d"},""))

Answer 2

So you can work with this:

Formula in cell C2: IF(AND(RIGHT(A2,1)=F2,LEFT(B2,1)=G2),VLOOKUP(RIGHT(A2,1),$F$2:$H$6,3,0),"")

So if the and returns true the vlookup finds and returns the letter.