Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine if data in one column is in another column

    I have a pair of tables, each with a column of URLs. I need to determine if a URL in the main table appears in the Usage table. I tried to do this with an index function, using match to determine the row. Example function: =INDEX(Usage, (MATCH([@URL], Usage[URL2],0)), (COLUMN(Usage[Views])))However, I got a value error when the Match function tried to evaluate the URL. My current hypothesis is that the URLs are too long to be evaluated this way. Suggestions on how else I could do this? I would strongly prefer to avoid VBA code if possible. Thank you, DeNae

  2. #2
    New Lounger
    Join Date
    Jan 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you just use a VLOOKUP?

    =VLOOKUP(A2,'table2'!A:B,2,FALSE)

  3. #3
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi dleverentz

    One way, which could be an option, for example your data is in A1:A100 and you have another list in B1:B75. Then in C1 and copy down to C100:
    =IF(ISERROR(MATCH($B1,$A$!:$A$100,0)),"",$B1)

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you both for your help. Unfortunately, neither solution works. In both cases, a #VALUE error is given. When I examine the calculation steps, it appears that the formula cannot resolve the (very long) URL. Is anyone aware of character limit in a single cell for formula evaluation? Thank you, DeNae

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    VLOOKUP and MATCH have a 255 character limit in the strings being searched for.

    Writing a UDF to find an exact match in a range is straightforward . . . along the lines of

    For each cell in a range, does the cell equal the searched-for cell ?
    This method avoids the character limit.

    However the "=" Function doesn't have the 255 character limit so, not using VBA, you could construct a table with the cells you are searching for on one axis and the list you are searching in on the other axis. I have attached a simple example - its works for a 572 character string . . .
    Attached Files Attached Files
    Last edited by MartinM; 2013-01-16 at 17:20.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts

    An alternative

    I would strongly prefer to avoid VBA code if possible
    Here is some code that I pieced together to find matching cell values equal to whatever the active cell is. It is very easily accessed by right clicking on the cell and selecting the macro from the context window. See image. You can cycle through all the matching cells and it will match internal and external hyperlinks.

    Context.jpg

    To make things easy, you can just copy your worksheets from your existing workbook into this workbook then rename it. If the formulas that the other members presented don't work for you, then use this as your last alternative. Any reason that you do not want to use the built-in Find function within Excel as it will also match hyperlinks?

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2013-01-19 at 10:43. Reason: spelling

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •