Google Sheets multiple IF(COUNTIF()) possible in same formula?

Question

I have been googling multiple hours now and still cannot find a solution for a Google Sheets problem.

I have two images, the first image is in the cell A1 and the cellname is called "Image1". The second image is in B1 and the cellname is called "Image2". I have two columns of set names, all names in column1 are related to Image1, all names in column2 are related to Image2.

Column1: K1:K4

Column2: L1:L4

I want the name-related images to be displayed next to the names I write down in another column according to if its a name from column1 (Image1 should be displayed next to it) or a name from column2 (Image2 should be displayed next to it).

D4 = First cell where I start writing down names from column1

G4 = First cell where I start writing down names from coulmn2

If I only write down names from column1 I have no problem to display Image1 next to it. Same with names from column2.

I am using those formulas:

=IF(COUNTIF($K$1:$K$4,D4),Image1,"")

=IF(COUNTIF($L$1:$L$4,G4),Image2,"")

The images are displayed correctly to the related names.

Now, if I write and mix down names from both columns I don't know how to automatically display the correct image related to the name in the cell.

Is it possible to combine =IF(COUNTIF)) multiple times?

GoogleSheetsSample

Answer 1

Yes, you can use them together like this in cell J12 on your test sheet:

=IF(COUNTIF($K$1:$K$4,I12),Image1,IF(COUNTIF($L$1:$L$4,I12),Image2,""))

An arrayformula will save you the trouble of dragging down the formula. Add this to cell J12 and delete all content in J13:J1000:

=arrayformula(IF(COUNTIF(K:K,I12:I),Image1,IF(COUNTIF(L:L,I12:I),Image2,"")))

For the arrayformula to work, the criterion for each countif needs to be from the cell where the formula is located (eg, J12 in this example), to wherever you want the criterion to keep working to. I've deliberately left it to :J so it works to the bottom of the sheet.

The countif ranges don't need to be fixed and they can be the entire columns if you wish K:K and L:L. Or you could set a range if you don't want the countif to go past a certain row (eg. K1:K9 and L1:L9).