Comparing two columns using partial match logic [Solved/Closed]

Posts
2
Registration date
Monday October 22, 2018
Last seen
October 22, 2018
- - Latest reply: patallen8845
Posts
2
Registration date
Monday October 22, 2018
Last seen
October 22, 2018
- Oct 22, 2018 at 05:52 PM
I need a bit of help please. I have 2 columns of data that I am doing a partial match compare (first 15 positions) using a formula in column AA.

My formula is as follows

=IF(MATCH("*"&LEFT(D2,D15)&"*",$Z$Z2:$Z$432,0)," ",D2)

Net is that I am checking the first 15 positions of column D row 2 and if there is a match I want column AA to be populated with the text from column D2. However, that is not happening.

When the criteria is not met #NA shows in column AA which is fine.

However when the criteria is met I end up with blanks in column AA.

Could someone please have mercy on my soul, and please educate me on what I should correct in the above formula so that I get results in column AA.

Thank you.
See more 

1 reply

Best answer
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1891
1
Thank you
It is the second blank entry between the " ".

=IF(MATCH("*"&LEFT(D2,D15)&"*",$Z$Z2:$Z$432,0),AA,D2)
-----------------------------------------------------------------------------------^^^^-----

If syntax is:
=If(Logic_statement, true, false)


If we break down your formula, we get:

If(
MATCH("*"&LEFT(D2,D15)&"*",$Z$Z2:$Z$432,0) //Logic_statement
" ", //True
D2) //False


Have fun!


Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 4484 users have said thank you to us this month

patallen8845
Posts
2
Registration date
Monday October 22, 2018
Last seen
October 22, 2018
-
Thank you for the recommendation. I changed the logic flow to your recommendation =IF(MATCH("*"&LEFT(D2,15)&"*",$Z$2:$Z$432,0),D2) and it works like a charm--Logic is check the first 15 characters of data in column D against the data in column Z AND if there is a match then populate AA with the data in Column D and if there is no match then default to #NA. Thank you.