Excel – How to use if condition in excel with list

microsoft excel

Requirement

I have different categories and I want to check if it falls within that categories then display that name in cell.
For example, here Name, Place,Thing are list, I want cell A10,A11,A12 to get fill with the category name that is mention in C10,C11,C12. i.e. my final cell value after giving value of C10,C11,C12 it will be like this.

Final Output

As the list of each category will contain around 30-50 records so I want to know if there is any way where I can write it.

Please refer to the image for more details.

Best Answer

enter image description here

Write this Formula in cell A10 & fill down:

=INDEX(A$2:C$2,SUMPRODUCT(MAX(($A$3:$C$6=C10)*(COLUMN($A$3:$C$6))))-COLUMN($A$2)+1)

How it works:

INDEX, returns a value of the cell at the intersection of a particular row and column, in a given range.

Here MAX, returns the maximum number from a range of cells.

In the Formula SUMPRODUCT, returns the sum of the products of corresponding ranges or arrays.

COLUMN, returns the column number of a reference.

N.B.

As soon as you write values in cell Range C10:C12, formula will pull related Category names in cell Range A10:A12.

Related Question