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.

Who is Participating?
CompProbSolvConnect With a Mentor 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.
carlsiyConnect With a Mentor Commented:
The problem here is your column references have trailing white spaces.
using TRIM( ) function can fix this....

For Column B row 2


For column D row 2


For Column F row 2

byundtConnect With a Mentor 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.

You could alternatively use VLOOKUP formulas like:

I've attached a sample file showing both sets of formulas.
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.

All Courses

From novice to tech pro — start learning today.