# Excel - Faulty Formulae

To whom it may concern,

The yellow-highlighted columns of the attached file contain formulae that give me incorrect output; three columns in total. I think that I am missing smth simple and that there is one solution to rectify this issue. I struggled with this for a bit, but at this point, I think that I just need a second pair of eyes. Any help would be extremely appreciated.

-Maroulator
Example.xlsx
###### Who is Participating?

Commented:
I found one error, not sure if it covers all of the problems.
B2 formula starts with =IF(A2="SF"
A2 is actually equal to "SF "  (note the space)
When I delete the space at the end of A2, the result changes to S
You could replace A2 with Trim(A2) throughout your formulae.
Considering how often that would be used, I'd prefer to have a column that is the trimmed version of A.  For example, in H2 use =trim(A2) and then use H2 in the formulae.
0

Commented:
The problem here is your column references have trailing white spaces.
using TRIM( ) function can fix this....

For Column B row 2

=IF(TRIM(A2)="SF","S",IF(TRIM(A2)="PUD","P",IF(TRIM(A2)="COP","J",IF(TRIM(A2)="CON","C",IF(TRIM(A2)="LSE","L","M")))))

For column D row 2

=IF(TRIM(C2)="OO","P",IF(TRIM(C2)="IN","I","S"))

For Column F row 2

=IF(OR(TRIM(E2)="SFFR-15",TRIM(E2)="SFFR-30",TRIM(E2)="SFFR-20",TRIM(E2)="SFFR-40",TRIM(E2)="MOD-SFFR"),10,(IF(TRIM(E2)="ARM10",93,(IF(TRIM(E2)="ARM51",92,(IF(TRIM(E2)="ARM31",91,(IF(TRIM(E2)="BALLOON-7",5,(IF(TRIM(E2)="ARM71",97,(IF(TRIM(E2)="BRST-23",0,1)))))))))))))
0

Commented:
You could put the lookup tables in named ranges on a worksheet. You could then use really simple formulas, and any updating would be done to an easily understood table rather than a complicated formula. This is the way I would suggest doing it.
=IFERROR(VLOOKUP(TRIM(A2),tbClassification,2,FALSE),"M")
=IFERROR(VLOOKUP(TRIM(C2),tbOccupancy,2,FALSE),"S")
=IFERROR(VLOOKUP(TRIM(E2),tbMortgage,2,FALSE),1)

You could alternatively use VLOOKUP formulas like:
=IFERROR(VLOOKUP(TRIM(A2),{"CON","C";"COP","J";"LSE","L";"PUD","P";"SF","S"},2,FALSE),"M")
=IFERROR(VLOOKUP(TRIM(C2),{"IN","I";"OO","P"},2,FALSE),"S")
=IF(OR(TRIM(E2)={"SFFR-15","SFFR-30","SFFR-20","SFFR-40","MOD-SFFR"}),10,IFERROR(VLOOKUP(TRIM(E2),{"ARM10",93;"ARM31",91;"ARM51",92;"ARM71",97;"BALLOON-7",5;"BRST-23",0},2,FALSE),1))

I've attached a sample file showing both sets of formulas.
ExampleQ28266955.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.