Forum Discussion

RichardL's avatar
RichardL
Copper Contributor
Mar 12, 2025
Solved

Excel lookup help

Hi, I am trying to work out the formula in excel to look at a column (C12:C23) and if there is the same text more than once only return it once (A15).  Then repeat this in A16 but disregard the valu...
excel
Formulas and Functions
  • ExcelExciting's avatar
    ExcelExciting
    Mar 14, 2025

    You can achieve this using a dynamic array formula with the combination of the functions UNIQUE and FILTER.

    =UNIQUE(FILTER(C12:C23,C12:C23<>""))

    This will automatically spill down the results and display all unique descriptions from the range C12:C23, starting from cell A15.

    If this resolves your query don't forget Mark as Solution.

m_tarler's avatar
m_tarler
Bronze Contributor
Mar 14, 2025

note if you just want every value returned 1x then as noted by ExcelExciting below you can use the UNIQUE function.  You can pair that with the FILTER function to remove the blanks if you care or just use the UNIQUE function and it will give an extra blank line:

=UNIQUE(C12:C23)

BUT if you want only cases where the text appears MORE than 1x then try this:

=UNIQUE(FILTER(C12:C23, COUNTIF(C12:C23,C12:C23)>1))

 

Resources