CODEWITHSUNDEEP
excelspreadsheetvlookup
M06H
M06H

Reputation: 1783

match corresponding values in different column which has additional characters

Column A    Column B
1111    #1111/1
2222    #2222/1
3333    #4444/1
4444

Given I have column A and B in Excel...is there a way I can match A and B and derive at something like this:

Column A    Column B
1111    #1111/1
2222    #2222/1
4444    #4444/1

The values in column A need to be next to their corresponding value in Column B which has additional characters in the beginning and in the end of it.

I also need to find any additional values for example 3333 which doesn't have a corresponding value in column B and they should be removed.

Upvotes: 0

Views: 109

Answers (1)

Jorvik77
Jorvik77

Reputation: 352

This should do the search and match for you - it needs to be entered as an array formula

=IFNA(INDEX($B$2:$B$5,MATCH(TRUE,ISNUMBER(SEARCH(UPPER(A2),UPPER($B$2:$B$5))),0)),"")

To remove the blanks you can do this in another column which is also an array

=SMALL(IF($C$2:$C$5<>"",$A$2:$A$5),ROW()-1)

After that you can do an index match to get the matching value 

=INDEX($B$2:$B$5,MATCH(A2,$A$2:$A$5,0))

This one last one is a regular formula, not an array

Upvotes: 2

Related Questions