Menu
Ask a question Report

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

Posts
2
Registration date
Monday October 22, 2018
Last seen
October 22, 2018
 - Oct 22, 2018 at 03:09 PM - Latest reply:
Posts
2
Registration date
Monday October 22, 2018
Last seen
October 22, 2018
- Oct 22, 2018 at 05:52 PM
Excel Ask a question Report
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 

2 replies

Best answer
Report Respond to ac3mark
Posts
10861
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 17, 2018
 - Oct 22, 2018 at 04:23 PM
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!


Thank you, ac3mark 1

Something to say? Add comment

CCM has helped 1673 users this month

Report
Posts
2
Registration date
Monday October 22, 2018
Last seen
October 22, 2018
 - Oct 22, 2018 at 05:52 PM
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