# Thread: Determine if data in one column is in another column

1. ## 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. Can you just use a VLOOKUP?

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

3. 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. 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. 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 . . .

6. ## 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