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?
 
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
 
Ryan ChongCommented:
=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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ryan ChongCommented:
how's your Customer data looks like?

we need some sample data for verification
1
 
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 ChongCommented:
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 ChongCommented:
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 ChongCommented:
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:
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
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.