Forum Discussion
IFERROR & VLOOKUP / If Month formula issues
I copied and paste 2 formulas related to 1) IFERROR and VLOOKUP, and 2) IF/Month from a known good spreadsheet into a new spreadsheet, and they stopped working. I spent hours debugging then and found no success. Can someone provide some guidance?
=IFERROR(VLOOKUP(W2,'FOP Table'!A:D,3,0), IFERROR(VLOOKUP(W2,'FOP Table'!B:D,2,0),"TBD"))
=IF(MONTH(Y2)<=3,YEAR(Y2)&"_FQ4",IF(MONTH(Y2)<=6,(YEAR(Y2)+1)&"_FQ1",IF(MONTH(Y2)<=9,(YEAR(Y2)+1)&"_FQ2",(YEAR(Y2)+1)&"_FQ3")))
I appreciate your help.
7 Replies
Cells A2 to C2 are formatted as Text, so they display the formulas as text instead of evaluating them.
Select A2:C2.
Set the number format to General.
Select the cells one by one, press F2 then Enter.
The formulas in A2 and B2 will return a value, but the one in C2 will return #VALUE!. This is because the formula refers to MONTH(Y2) and YEAR(Y2), but Y2 does not contain a date.
If you apply General format (instead if Text) to these cells
and re-enter formulas (F2, Enter), they calculate something. But I'm not sure what you'd like to calculate. For example, formula in C2 refers on Y2 as on date, but that's text field.
Hi SergeiBaklan,
Thank you much for your help. It worked after I followed your instructions. Why would a General Format (instead of text) cause the problem? What does F2/Enter do to the existing formula?
Also, The formula in C2 should refer to Q2 (instead of Y2) as on date. Thanks for pointing it out.
Lee
If cell is formatted as text, whatever you enter into it will be taken literally as you typed it. Even if such text is in form of date, formula, number - that's just a text.
If you format the cell as General when Excel recognizes you enter something else. If this else start from = Excel interprets what was entered as formula.
If you press F2 you shift on cell edit mode (you may edit cell content directly within it, not in formula bar). Pressing Enter you finish the editing.
Above are main topics, something could be done by another way.
