问题描述:

I have been trying with little success to compare four columns containing url results to a golden list in excel.

I want to count all the matches in each column and give a running total per set. I have used VLOOKUP, Match and highlight duplicates none seem to be able to give the desired effect

Golden list Column A Column B Column C Column D

I want it to compare the values in the golden list against the other 4 columns and give a score count of all exact matches found. I have read several of the Excel Stack Overflow questions and used youtube with no luck so far.

网友答案:

Computing score for each item in golden list is as easy as:

=COUNTIF(range_of_all_columns, item_from_golden_list)

To get the score for each column you can either:

  • match each item in each column individually or
  • match each item in golden list against all of the columns respectively (if exact matches are sufficient)

For the second approach, you can use =IF(ISNUMBER(MATCH(...)), 1, 0) approach - if you have duplicated links in columns, or a simpler formula if they are unique:

=COUNTIF(range_of_1_column, item_from_golden_list)

..and then sum it all up for each column, as illustrated by following picture:

相关阅读:
Top