Forum Discussion
EXCEL-VLOOKUP
I have a little problem with formula: VLOOKUP. I write everything ok (I think so) but my formulas aren't fullfild.
I want to write that all time when somebody write their year of the birth, they will se with zodiac sign they have.
"vlook_value" is the year which I write in the cell above the cell with my main formula.
"table_array" is a squer of my table.
"col_index_num" is the number 1 where were written names of zodiac sign.
"range_lookup"- false or truth- neutral- all the time I see the mistake.
I don't know why this programm show me the mistake if In my mind everything is ok. Do you any mistake?
I added one photo with the screenshot.
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.
Thanks Sergei. It is the best way to return Zodiac name.
- EricStarker
Community Manager
Thanks for the question. I'm moving it to the Excel space for better visibility. 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),"")))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)
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.
