CODEWITHSUNDEEP
excelexcel-formula
Steve Meadows
Steve Meadows

Reputation: 21

Excel Formula not working

I am trying to create a formula in Excel that will take a letter from a box i.e A and then will take depending on which letter it chooses from the value associated with it. For example, if R12 displays the letter A it will select from box P12, if B from box P13 and so on up to H.

I am using the IF statement but it doesn't want to work and when it does it decides I have too many values so I have no clue how to make this work.

I have tried Googling it and have found loads of information but nothing that helps me with this so any help would be appreciated.

This is the code I am trying so far:

=IF(R12=A,P12,IF(R12=B,P13,IF(R12=U4,P14,IF(R12=D,P15,IF(R12=E,P16,IF(R12=F,P17,IF(R12=G,P18,IF(R12=H,P19))))))))

Upvotes: 0

Views: 66

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

The issue is that you are not using "" where you should

=IF(R12 = "A",P12,IF(...

So Excel think it is a partial cell address, not a string.

But for length use INDEX:

=INDEX(P12:P19,MATCH(R12,{"A","B","U4","D","E","F","G","H"},0))

Upvotes: 3

JNevill
JNevill

Reputation: 50308

So that you don't end up in nested IF hell, you could do something like:

=OFFSET(P12, MID("a1b2c3d4e5g6h7", FIND(R12, "a1b2c3d4e5g6h7", 1)+1, 1)-1,0)

Upvotes: 2

Related Questions