IFERROR & VLOOKUP / If Month formula issues

[Lee_Koh]

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.

[#2]

@Lee_Koh 

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.

[#3]

@Lee_Koh

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.

[#4]

Hi @Sergei Baklan,

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 

[#5]

Hi @Hans Vogelaar, thanks so much for your advice. I understand now. 

I learnt something today.

Lee 

[#6]

@Lee_Koh 

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. 

[#7]

I got it. thank you foe your help.

Lee

[#8]

@Lee_Koh , you are welcome