Forum Discussion

Copper Contributor

Oct 23, 2018

IF Formula trouble

Hi All I have a formula which lists names looking at a criteria in a cell. =IFERROR(INDEX('Year 5'!$A$3:$A$62,SMALL(IF('Year 5'!$C$3:$C$62="1",ROW('Year 5'!$A$3:$A$62)-ROW('Year 5'!$A$3)+1),ROWS...

excel

Formulas and Functions

erol sinan zorlu

Iron Contributor

Oct 23, 2018

Hello,

have you tried using Countif formula?

=countif('Year 5'!$A$3:$A$62, $A1)

the second range should be the staring cell address of your second list. If the formula returns 1 or more then this name is already exists in your main table.

Ian Tobin
Copper Contributor
Oct 23, 2018
Hi
I am not wanting to count the names. The formula at the moment looks at the list of names in a3:a62 and makes a new list of any names that have a one in their corresponding cell in column c. I still want to return the names but the ones that have a 1 in column c and column I.
- erol sinan zorlu
  Iron Contributor
  Oct 23, 2018
  Hello,
  
  can you add your excel file? and describe what you want to achive? As I have understood you have C and I colomns with some values. and you want to look for names where there is 1 on the correspondig cells in C and I column? Is it correct?
  - Ian Tobin
    Copper Contributor
    Oct 23, 2018
    Yes, but when those values are found it then lists the names from column a in the cell and the cells below it. Ill try and make a simplified version as there is a lot of data in the sheet I am using and post it.