-1

I have a formula I need some assistance with. What I am trying to achieve is to check if the text in Cell A appears anywhere within the Cell C Column, if the text does appear then I want excel to print the data from the matching Cell C. What I have so far is;

=IF(ISERROR(MATCH(""&A3&"",$C$2:$C$8,0)),"",A3)

Now this formula does what I want but it prints the contents of Cell A, it is not simply a case of changing it to Cell C as that won't print the matching cell.

I'm sure this is something simple and thankyou in advance for your responses.

sointeriors
  • 1
  • 2

1 Answers1

1

You need to use INDEX to get the contents of the matching cell in column C.

If you have a recent version of Excel (2007 onwards), you can use IFERROR to check if the match has succeeded:-

=IFERROR(INDEX($C$2:$C$8,MATCH("*"&A3&"*",$C$2:$C$8,0)),"")

If not, use ISERROR as you have done followed by an INDEX function:-

=IF(ISERROR(MATCH("*"&A3&"*",$C$2:$C$8,0)),"",INDEX($C$2:$C$8,MATCH("*"&A3&"*",$C$2:$C$8,0)))

You can also use VLOOKUP:-

=IFERROR(VLOOKUP("*"&A3&"*",$C$2:$C$8,1,FALSE),"")

or

=IF(ISERROR(VLOOKUP("*"&A3&"*",$C$2:$C$8,1,FALSE)),"",VLOOKUP("*"&A3&"*",$C$2:$C$8,1,FALSE))

If the strings in C2:C8 are longer than 256 characters, you can use the approach suggested Here. Using FIND for partial matching, this would give the formula:-

=IFERROR(INDEX($C$2:$C$8,MATCH(TRUE,ISNUMBER(FIND(A3,$C$2:$C$8)),0)),"")

which has to be entered as an array formula using Ctrl-Shift-Enter.

If you don't want it to be case-sensitive, use SEARCH instead of FIND. As before, you can also use IF(ISERROR... for backwards compatibility.

Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Unfortunately neither of these solutions work on Excel 2011 for MAC – sointeriors Feb 01 '15 at 18:13
  • I've edited it so the lookup text in A3 can just be part of the text in column C. Sorry I can't test it on a MAC. – Tom Sharpe Feb 01 '15 at 19:45
  • Hi Tom, thanks for the help and this is now working however it does't work when the C cell is greater than 256 characters. How can I get around this? – sointeriors Feb 01 '15 at 22:23
  • I think using the approach suggested by @Barry Houdini http://stackoverflow.com/questions/13202473/way-to-overcome-excel-vlookup-function-limit-of-256-characters . I will add it to my answer. – Tom Sharpe Feb 02 '15 at 10:18
  • That new formula won't enter as an array formula – sointeriors Feb 02 '15 at 21:48
  • It's fine in Excel 2010 on Windows 7. I've also tested it in Google sheets. If it's to do with entering an array formula on the MAC, there is a discussion of key combinations at http://answers.microsoft.com/en-us/mac/forum/macoffice2011-macexcel/create-array-in-excel-using-mac-keyboard/44368fab-ff59-452f-bc7d-d4a30116e9a3 – Tom Sharpe Feb 06 '15 at 09:14