EXCEL-VLOOKUP

My guess

=OFFSET($A$2,MOD(($F$15+8),12),0)

is enough if the purpose is to return Zodiac name. If to train in VLOOKUP - that's another story.

 

 

From https://support.office.com/ , VLOOKUP formula is

=VLOOKUP(Value you want to look up, 
         range where you want to lookup the value, 
         the column number in the range containing the return value, 
         Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE)

The first parameter of VLOOKUP is the value that you want to look up value.  The second parameter is the range where you want to lookup the value, but, the first column must be the column in which the look up value is.  In your case, it is not easy to know which column contains the lookup value, and the most important part is that the column would not be the first column of the range.  Hence, VLOOKUP formula is not suitable on your case.

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)

 

 

 

 

 

Vlookup is comparing 2000 (Cell E15) to values in A2:A13 (Zodiac names). It's not finding a match and thus you are not getting the expected results. Unpivot that list and put year in the first column and Zodiac name in the 2nd column then reference that list in your vlookup and change the column returned from 1(year) to 2(Zodiac Name).

 

There is a way to do it without un-pivoting the data but that is more complicated. Using your screenshot example it would be:

=INDEX($A2:$A13,CONCATENATE(
IFNA(MATCH($E$15,B2:B13,0),""),
IFNA(MATCH($E$15,C2:C13,0),""),
IFNA(MATCH($E$15,D2:D13,0),""),
IFNA(MATCH($E$15,E2:E13,0),""),
IFNA(MATCH($E$15,F2:F13,0),""),
IFNA(MATCH($E$15,G2:G13,0),""),
IFNA(MATCH($E$15,H2:H13,0),""),
IFNA(MATCH($E$15,I2:I13,0),"")))

Thanks for the question. I'm moving it to the Excel space for better visibility.