Hello all,



I have a couple issues I need help with. I am attempting to populate data into Column D and G in the attached spreadsheet.

The formulas I have, pulls the code from A, compares it to H, and returns what is in I into C. When I attempt to pull the rows that have multiple codes, and populate into D, it gives me an error because there are spaces at the end of the code in A. If I manually remove them, it works, but when I attempt to add TRIM, it doesn't do anything, so I get an error.

The same thing is happening when I attempt to do the same for column G for the ones with three codes. There are a couple in row B that have 4 different codes that I am going to work on that once I get this under control.

Any help you all can provide would be greatly appreciated.

Attached Files

Test1234.xlsx (115.9 KB, 11 views)

Into D2 enter formula
and copy down!

Use SUBSTITUTE in 2nd formula too!

I don't see why the SUBSTITUTE in col D is necessary
does the job


@tispivey


Using entire columns as reference is bad practice
Use Excel Tables instead as dynamic reference

As for the IFERROR function, do not apply it before you have ascertained that your formula works correctly
In this case if you want to hide the NA error, OK, but suppose, for some reason, it returns a VALUE error? IFERROR will hide that also leading to incorrect results
IMO the ISNA function is safer

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

That worked! Thank you!

I have tried ISNA and IFERROR, and IFERROR seems to work better for what I am using this for. This report goes to a lot of VPs in the company i work for, and the cleaner it is, the better, but I truly appreciate the feedback.

Please mark thread as " solved". thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks for the rep

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post