Menu

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

Report
Posts
2
Registration date
Monday October 22, 2018
Status
Member
Last seen
October 22, 2018
 -
patallen8845
Posts
2
Registration date
Monday October 22, 2018
Status
Member
Last seen
October 22, 2018
 -
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

Answer 1 / 1
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
 1633
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!


Thank you
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5515 users have said thank you to us this month

Report
patallen8845
Posts
2
Registration date
Monday October 22, 2018
Status
Member
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.
Ask a question