0

I need a bit of help finishing of this formula:

=IF(ISERROR(MATCH("*"&C2&"*",B:B,0)),"False","True")

I have a list of addresses some with multiple In one cell, with a lot containing a postcode at the end. I have a separate list of postcodes that I have searched on using the wildcard function. So I can get whether somewhere in my list their is a matching postcode in the first list. I then have to match up the unique reference (column A) with my list of postcodes. This is the sample data(I have separated the columns using -):

LA77648 - 22 Dane Street, Burnley (BB10 1AB)  - BB10 4QL -  True
LA80291 - 74 Burns Street, Burnley (BB12 0AJ) - BB10 3EZ -  False
LA81235 - 76 Westgate, Burnley (BB11 1RY)     - BB12 8HY -  False
LA87469 - 17 Branch Road, Burnley (BB11 3AT)  - BB11 5HT -  False

This is so close to what I want but I need another column after the True/False to copy the relevant matching cell. I then need the next column to show me the cell in column A that matches the matching cell in column B. I have tried Index, VLookup, Matches, Formatting and everything else I can think of.

This is an example of what I need it to look like (done manually):

BB11 3JA - True - 2 St Stephens Street, Burnley (BB11 3JA) - LA123865
BB12 0NL - True - 4 Cairo Street, Burnley (BB12 0NL)       - LA297035
3
  • I can get whether the postcode in column C matches any part of any cell in column B. Hence the True or False. However what I need is the actual address in that cell. However I do not know the end of the excel formula to give me this. So in normal speaking terms I need "does this postcode exist in column B. If it does copy that cell where it exists. Then give me the data in the adjacent cell in Column A"
    – Mark
    Aug 30, 2016 at 10:20
  • Why is BB10 4QL True? Aug 30, 2016 at 11:02
  • BB10 4QL isn't actually True it was just a sample list to show the difference between the True and False. The 2 lists I have are over 7000 rows so I quickly made a sample list. The above example are True results as they match the text in column B
    – Mark
    Aug 30, 2016 at 11:13

2 Answers 2

1

Try:

=IF(ISERROR(MATCH("*"&C2&"*",B:B,0)),"False",INDEX(B:B,MATCH("*"&C2&"*",B:B,0)))

in column D and:

=IF(ISERROR(MATCH("*"&C2&"*",B:B,0)),"False",INDEX(A:A,MATCH("*"&C2&"*",B:B,0)))

in column E.

This uses the row number found in your original match formula to pull out the relevant data from the other columns.

1
  • Worked like a charm. I actually tried this kind of formula with index but didn't put the match in there. Thank you very much for your help.
    – Mark
    Aug 30, 2016 at 11:23
0

You can get rid of the true column you want, or just base these on the result of the true

=IFERROR(INDIRECT("B" & (MATCH("*"&C4&"*",B:B,0))),"")
=IFERROR(INDIRECT("A" & (MATCH("*"&C4&"*",B:B,0))),"")
1
  • Also worked as well but I don't understand why. Many thanks.
    – Mark
    Aug 30, 2016 at 11:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .