Excel Formula Question

Hello Experts,

I have a question regarding formulas in Excel (2013). Please excuse my novice level of Excel formula usage.

I have data in a worksheet such as:

Column A = Old Cust ID
Column B = New Cust ID

I am trying to find matches using this formula (simplified for conversation sake):

=IF(MATCH(A2,B$2:B$10000,0),"YES","NO")
 
Not sure if this matters, but the data source for the A column is a SQL DB. The data source for the B column is an Access DB.

Oddly enough, this formula work s all of the time on one worksheet, but if I copy the cells and formula to a new worksheet, things get weird as I described.

Sometimes, I can click in the cells that contain the matching data that I can clearly see, and Excel appears to 'format' the contents of the cell by shifting it right if it is left, so not sure if this may be a factor. The Number Format is set to General on all cells.

Can someone tell me what I might be doing wrong? Could this be some hidden formatting due to the different data sources used for the import?

Here is the actual formula that works:
=IFERROR(INDEX(TRIM(Customers!D$2:D$50169)

On another worksheet, I'm also trying to perform the inverse, but this formula does not work:
=IFERROR(INDEX(TRIM(Customers!D$2:D$50169),MATCH(B2,TRIM(Customers!C$2:C$50169),0)),"")

This is the problem that led me down this rabbit hole.

Any insight would be greatly appreciated. Thanks in advance for your expertise!
Jerry D.Systems/Network EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
=IFERROR(INDEX(TRIM(Customers!D$2:D$50169)
is this a complete formula? it seems it will generate error as it seems incomplete...

are you using array formula in your worksheets? can you post a sample here?
0
Jerry D.Systems/Network EngineerAuthor Commented:
Sorry Ryan, that first comment only contained a portion of the formula.
Here is the formula that works:

=IFERROR(INDEX(TRIM(Customers!D$2:D$50169),MATCH(B2,TRIM(Customers!C$2:C$50169),0)),"")

This formula works.

However when I switch the column values on another worksheet such as this:

=IFERROR(INDEX(TRIM(Customers!C$2:C$50169),MATCH(A2,TRIM(Customers!D$2:D$50169),0)),"")

That formula does not work. And it seems there is no reason why it does not work. Which is why I am wondering if this could be format related.

Sorry for the initial confusion, and thanks again for your expertise!
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
how's your Customer data looks like?

we need some sample data for verification
1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jerry D.Systems/Network EngineerAuthor Commented:
Sorry I can't share the data, it is Personally Identifiable Information.

I'm filtering the two IDs and pulling a value, like a name if they match. Basically I'm looking at a column of old IDs and then comparing if they match, to give me the new ID:
=IFERROR(INDEX(TRIM(Customers!D$2:D$50169),MATCH(B2,TRIM(Customers!C$2:C$50169),0)),"")

Then in the second sheet, I am validating the old ID by doing just the reverse (by switching the column letters around in the formula):
=IFERROR(INDEX(TRIM(Customers!C$2:C$50169),MATCH(A2,TRIM(Customers!D$2:D$50169),0)),"")
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
well, you can provide dummy data :)
0
Jerry D.Systems/Network EngineerAuthor Commented:
Sorry Ryan, I don't see where that is relevant. If you can't answer the question, then I'll wait for someone else. Thanks for your effort.
I was mainly asking about the structure of my formula. And if I am approaching it the correct way.
Also, I was hoping someone would respond regarding the formatting issue - i.e. is there some keyword or some operand I should use to tell Excel to ignore all formatting in all cells when processing the formula?
Again, thanks for your effort.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
yea, experts may don't have time to build the scenario to simulate the issue from scratch so they're asking whether asker can also take his/her part to provide useful info as much as possible.

well, you got to wait for someone willing to assist with limited info.
2
Jerry D.Systems/Network EngineerAuthor Commented:
Wow. Thanks anyway. No one is asking you to respond. In fact, please do not. Your rude and unprofessional response makes me want to cancel my subscription to Experts Exchange if you are representative of an "Expert". I feel like I gave enough information to at least get a response on the syntax of my formula, and also to get a response on if this is format related. You don't need sample data for that.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
nope, i'm not rude and good luck to you
2
Jerry D.Systems/Network EngineerAuthor Commented:
Here's some 'dummy data' to save time in case my explanations aren't clear in the initial post.

Column A        Column B      Column C       Column D
1                        33                    3
2                        17                    7
3                        25                    5
4                        22                    4                         22

So basically in this 'dummy data' I am saying if the ID in Column A matches the ID in Column C, to then return the ID that is in Column B into Column D.
This is the first formula I shared, and that one works.
Then for the second formula, I am validating the returned value in Column B, by reversing the syntax. This does not work. That was my formula question.

While waiting for a valid answer, I investigated the formatting issue more. As I said initially, I can click in the cells that contain the matching data that I can clearly see, and Excel appears to 'format' the contents of the cell by shifting it right if it is left, so not sure if this may be a factor. The Number Format is set to General on all cells.
I have further discovered that with the data left justified in all cells, the formula works. If I click into any cell, Excel automatically 'formats' the data by moving to the right justified position. The formula then does not work. This seems to indicate that the problem is with the formatting of the cell.
My formatting question is: Does Excel contain an operand or function for a formula (like TRIM) that will tell the formula to ignore ALL formatting in the cells and just work with the raw data?
Is that too limited of an explanation for help from this community?

Could someone other than Ryan please answer this question.
0
NerdsOfTechTechnology ScientistCommented:
I recommend referencing the data on the other sheet instead of copying the data.

E.g. from Sheet2 reference =Sheet1!. So far it looks like you are doing that with Customers!

I think the other expert focused on a potential logic error with the INDEX function:

In Microsoft Excel is:
INDEX( table, row_number, column_number )

Open in new window


 =IFERROR(
      INDEX(
            TRIM(Customers!$C$2:C$50169),
            MATCH(A2,TRIM(Customers!$D$2:D$50169),0)
            *missing OPTIONAL 3rd parameter column_number*
            )
 ,"")

The missing 3rd parameter might be the missing link from the new formula working.
1
Jerry D.Systems/Network EngineerAuthor Commented:
Ah Hah! I just figured it out. I have to use VALUE instead of TRIM in the MATCH portion:

=IFERROR(INDEX(TRIM(Customers!C$2:C$50169),MATCH(A2,VALUE(Customers!D$2:D$50169),0)),"")

That works, in case anyone is interested.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry D.Systems/Network EngineerAuthor Commented:
Thanks NerdsOfTech. Very helpful info as well!
0
Jerry D.Systems/Network EngineerAuthor Commented:
As I explained initially, Excel is not my forte. I am a Cloud Systems and Network Engineer, and do not have 100s of fake certs, but real-world experience. I normally receive very helpful info from this community, but Ryan Chong was very rude and unprofessional. I even had coworkers onlooking as I was asking this question and they viewed his condescending responses. Ryan is in no way, an Expert, by any standards.
Thank you to Expert NerdsOfTech for a professional helpful response!
0
NerdsOfTechTechnology ScientistCommented:
Good to know that VALUE() works because I was wondering if it worked on RANGES myself. Probably text(RANGE, "0") might work as well too. Thanks for the points.

Having sample data and expected output was extremely helpful.

So basically in this 'dummy data' I am saying if the ID in Column A matches the ID in Column C, to then return the ID that is in Column B into Column D.

In your sample scenerio, for column D2:D50169 you would modify the formula to:
=IFERROR(INDEX(VALUE(TRIM(Customers!$B$2:$B$50169)),MATCH($A2,VALUE(TRIM(Customers!C$2:C$50169)),0)),"")

Open in new window

1
Jerry D.Systems/Network EngineerAuthor Commented:
Yessir that works exactly as expected. Thanks again for your professional expertise!
0
Jerry D.Systems/Network EngineerAuthor Commented:
I figured it out based on a bad response from an 'Expert' who clearly was rude and unhelpful. The second expert that answered was very helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.