IF(ISERROR(MATCH) problem

[sin303]

This is driving me nuts. I'm sure its pretty easy but have not been able to find the solution.

I have two columns of text data ( song titles) I want to see which title from column A also appears in column B.
What is challenging is that the titles are not exact matches, although the first 5-6 characters are the same. I have been using the following but it only works for exact matches.

can someone please help.




=IF(ISERROR(MATCH(C3,$H$1:$H$339,0)),"",C3)

[AlKey]

See if this will work

Instead of C3 use this:

LEFT(C3,5)&"*"

or see if this formula will work for you

=INDEX(C:C,MATCH(LEFT(C3,5)&"*",$H$1:$H$339,0))

	C	D	E	F	G	H
3	POP Music			POP Music		POP Music new

[Speshul]

Can you post some sample data?

Also, you can use IFERROR instead of an IF(ISERROR()) combo

=IFERROR(YourFormulaHere,"")
That will leave a blank if the formula produces an error.

[sin303]

Hey alkey, that didn't work.

[ben_hensel]

Also, you can use IFERROR instead of an IF(ISERROR()) combo

OP has listed MS version as "XP", which implies Windows 2003 (or maybe even earlier); IFERROR was introduced with 2007.

Hey alkey, that didn't work.

Did you adjust the example formula to match your specific range references?

[sin303]

I do have windows 2007, let me give it another try

[sin303]

here is an example of what i'm doing.

what i used first =IF(ISERROR(MATCH(C3,$H$1:$H$339,0)),"no match",C3)
Alkey's suggestion was pretty close but not quite.


as you can see the titles are not exactly the same although the first few characters are.


B C G H
Artist Title Artist Title
10cc Dreadlock Holiday (1978) 10cc Dreadlock Holiday
10cc I'm Not In Love (1975) 10cc The Things We Do For Love
10cc People In Love (1977) A Taste Of Honey Boogie Oogie Oogie
10cc I'm Mandy Fly Me (1976) Abba Eagle
10cc Good Morning Judge (1977) Abba The Name Of The Game
10cc Donna (1973) Abba Ring Ring
10cc Art For Art's Sake (1975) Abba Chiquitita
10cc Dreadlock Holiday (1978) Abba Dancing Queen
5000 Volts I'm On Fire (1978) Abba Knowing Me Knowing You
Abba Dancing Queen (Extend Rmx)1976 Abba Does Your Mother Know
Abba Mamma mia (1976) Abba Fernando
Abba Knowing me knowing you (1977) Abba Waterloo
Abba Chiquitita (1979) Abba Take A Chance On Me
Abba Waterloo (1974) Abba Mamma Mia

[AlKey]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[sin303]

ok I've attached a file with a small portion of the database. I want to see the duplicates of same cells from C column & H column, even though the cell info is not exactly the same. they do start the same, so I was trying to use LEFT(C4,5) but could not get it to work.


thanks

[AlKey]

With your formula

=IF(ISERROR(MATCH(LEFT(C3,5)&"*",$H$1:$H$339,0)),"no match",C3)

	L	M
2	Your Results	New Results
3	Dreadlock Holiday (1978)	Dreadlock Holiday (1978)
4	no match	no match
5	no match	no match
6	no match	no match
7	no match	no match
8	no match	no match
9	no match	no match
10	Dreadlock Holiday (1978)	Dreadlock Holiday (1978)
11	no match	no match
12	no match	Dancing Queen (Extend Rmx) (Andy7) 1976
13	no match	Mamma mia (1976)
14	no match	Knowing me knowing you (1977)
15	Chiquitita (1979)	Chiquitita (1979)
16	no match	Waterloo (1974)
17	no match	no match
18	no match	The name of the game (1977)

[sin303]

thats exactly it, thank you very,very much. I wish I could give you tons of reputation points. you're the man!

[AlKey]

You're welcome
You should've taken my advice given in my first post to use LEFT(C3,5)&"*" instead of just C3