MATCH Function Producing Erratic Results

[brent_s]

I would like to use the MATCH function to compare two columns of data and alert me when there is a disparity. I get mixed results. Some rows function as expected, others fail to catch the error. In testing, I purposely change the data in column D so it doesn't match E expecting to get an error producing the 'if true' result (Room Change). Extensive testing has revealed no discernible pattern. Sometimes changing the number in column D by just one or two numbers (up or down) produces the expected results whereas a larger number change, say 100, fails to produce an error. I've even tried trimming the number to no avail. Columns D and E contain numbers prefaced by a letter as in C-305. I'm stymied.

IF(ISERROR(MATCH(D2,E$2:E$196,0)),"Room Change","")

 

[NBVC]

Is that C - 305 actually a number formatted to show the C - in front?

If so, then it could be a rounding issue, and you might need to employ a ROUND() function to get more precision in matches.

If not, then there might be some leading or trailing spaces are even extra spaces around the dash?

Otherwise, please post a sample workbook showing the problem (please exclude confidential info).

 

[brent_s]

Thanks NBVC for your response and willingness to help me with this issue. Yes, the letter C- is part of the number and has no space between the letter, dash, and number. I use VLOOKUP to match names from another worksheet to populate column E with the relevant data. The IF function, using MATCH as the logical test, is one of three in column F designed to respond to four possible conditions between the two columns. I've attached the pertinent workbook that hopefully makes more sense than me trying to explain it here. Row 106 is what led me to investigate the MATCH function failing to produce the expected result (Room Change).

Brent

 

[NBVC]

Maybe it is the order that you nest the IF statements? Because it seems that as it is, it is returning the results as per the formula as written?

Note, IF statements work from Left to Right. As soon as one of the IF statements results in TRUE, then it returns that IF_TRUE result and stops without checking further statements...

could that be the problem?

Maybe if you dictate in English what the actual requirements and priorities are, then a formula can be written to match.

 

[brent_s]

OK, here's the logic:
Do the names NOT match (produce an error)?

Yes--Does D = OOB
​

Yes --enter "OOB"
​

No--enter "Moved"
​

No--Does the Apt NOT start with D or C-3
​

Yes--enter "Wrong Bldg/Flr"
​

No--Do the Apts in D & E NOT match
​

Yes--enter "Room Change"
​

No--enter empty string
​

I believe the logic is correct. Each nested IF is evaluated only if the previous IF produces a False result.

The problem lies with the last IF statement. Again using row 106, D & E clearly contain different Apts which should produce an error and therefore a True evaluation for the IF function. However, it instead returns a False. Changing the number in column D on other rows produces mixed results. Sometimes it will evaluate as True, other times it remains False.

Brent

 

[NBVC]

How about?

=IF(ISERROR(MATCH(D2,E$2:E$188,0)),IF(D2="OOB","OOB","Moved"),IF(AND(LEFT(E2,1)<>"D",LEFT(E2,3)<>"C-3"),"Wrong Bldg/Flr",IF(D2<>E2,"Room Change","")))

copied down.

Note there are a couple of #N/A errors produced. This is because the D column found a match in column E, but there is an N/A in E at that row, and so can't match D to E.... Do you want another message when that is the case?

Also, what version of Excel are you in?

 

[brent_s]

In the original formula, the first thing I did was test for #N/A. If that was the case I didn't need to go beyond the test for OOB. Your suggested rewrite eliminates that. I'll need some time to evaluate the changes and get back to you. Need to leave for awhile so it will be 3 or more hours. I'm using Excel version 14, part of Office 2010.

 

[NBVC]

The thing is what does having the #N/A mean? it's not wrong room, it's not the OOB or Moved and it's not a Room change... you can make it blank, leave it as #N/A (so that you know you need to look into it) or you can combine it with one of the above resulting strings... your choice....

 

[brent_s]

Interesting observation. I was using the #N/A to let me know the VLOOKUP failed to find a name match and then provide the appropriate response. After looking over your code, I realized I was using MATCH incorrectly. I was trying to do what you did with the "does not equal" operator. After correcting my code, it looks like this.

=IF(ISERROR(E2),IF(D2="OOB","OOB","Moved"),IF(AND(LEFT(E2,1)<>"D",LEFT(E2,3)<>"C-3"),"Wrong Bldg/Flr",IF(D2<>E2,"Room Change","")))

How utterly simple. I should have caught that. I kept the check for #N/A to bypass the rest of the IF statements if further checks weren't needed. Unless you can see a way to simplify, this works as expected. Thanks for all your help.