EXCEL-VLOOKUP

Hi Natalia,

Actually, I would say that you are using the selected year (somebody write their year of the birth) to find out zodiac sign.  To speak in more detail, using the selected year to find out the row of the year of birth and following the row to find out zodiac sign.  

Using the following formula, we can find the matching cell

=($B$2:$I$13=$E$15)

If you press F9, you can see there is only one "TRUE" value.  

As I mentioned before, we are seeking the row of the year of birth, we can based on the matching cell to get the row by the following formula

=($B$2:$I$13=$E$15) * ROW($B$2:$I$13)

 Then, we will get something like 
{0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0;0,0,0,0,0,0,6,0;0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0}

You can see 6 in the above array and, other than 6, it is all 0 (zero).

We can use SUMPRODUCT formula to sum up all these values.

=SUMPRODUCT(($B$2:$I$13=$E$15)*ROW($B$2:$I$13))

What will we get? yes, it is 6, which is 6 + all 0s.

With the help of INDEX formula, we can follow the row to get zodiac sign

=INDEX($A$2:$A$13,SUMPRODUCT(($B$2:$I$13=$E$15)*ROW($B$2:$I$13))-ROW($A$2)+1,1)