Nested IF Statement with IFERROR and VLOOKUP

AKBP

I am trying to write a formula that will pull sales data from one sheet (FY21 Sales) into another (OPT) based on the fiscal year in the OPT sheet and matching project numbers in both the FY21 Sales sheet and the OPT sheet.

For example, if project number 1234 listed on the OPT has a FY of 2021, then I want the OPT to search the FY21 Sales sheet for project number 1234 and pull any sales data that is associated with that project number. If there is no matching project number and data cannot be pulled from FY21 Sales, then I want a zero to appear in the cell where the sales data would have been on the OPT sheet.

So far, I have managed to use an IFERROR/VLOOKUP formula to pull the sales data, however I cannot seem to nest it properly with the IF statement to incorporate the fiscal year portion of my formula. The formulas I am currently trying to nest are below.

=IFERROR(VLOOKUP([Project Number]@row, {NSD November FY21 Sales Range 1}, 3, false), 0)

=IF([Estimated Award FY]@row = "2021", 0, 1)

Any help is appreciated. If there is a different way to do this, I would love to know how.

Thank you!

Genevieve P.

Hi @AKBP

It sounds like you want to use your IF statement to check in this current row to see if the Estimated Award FY is 2021, and only pull the VLOOKUp information if this statement is true, is that correct?

If so, you can replace the 0 in the IF statement to be the entirety of your first formula. Try this:

=IF([Estimated Award FY]@row = "2021", IFERROR(VLOOKUP([Project Number]@row, {NSD November FY21 Sales Range 1}, 3, false), 0), "Not 2021")

I may be misunderstanding your set-up so it would be good to see screen captures (if possible, but please block out any sensitive data).

Cheers,

Genevieve

Lauren Kleitz

I think this will do what you are looking for!

=IF(NOT(ISERROR(VLOOKUP([Project Number]@row, {NSD November FY21 Sales Range 1}, 3, false))), VLOOKUP([Column4]@row, [Project Number]:Sales, 2, false), 0)

Basically what this does is runs the VLOOKUP and checks to see if it is NOT an error. If it is indeed not an error (meaning it exists in your lookup range), then it pulls in the lookup value. If it is an error, then it returns a 0.

Let me know if that helps/makes sense!

-LK

AKBP

Hi Lauren,

This makes sense, but I need to incorporate the 2021 fiscal year in there somewhere. I only want it to pull sales data if the sale is in 2021. I have other potential sales listed for future years. Does that make sense?

Thanks for your help!

Genevieve P.

Hi @AKBP

It sounds like you want to use your IF statement to check in this current row to see if the Estimated Award FY is 2021, and only pull the VLOOKUp information if this statement is true, is that correct?

If so, you can replace the 0 in the IF statement to be the entirety of your first formula. Try this:

=IF([Estimated Award FY]@row = "2021", IFERROR(VLOOKUP([Project Number]@row, {NSD November FY21 Sales Range 1}, 3, false), 0), "Not 2021")

I may be misunderstanding your set-up so it would be good to see screen captures (if possible, but please block out any sensitive data).

Cheers,

Genevieve

AKBP

@Genevieve P ,

That worked perfectly! Thank you! My colleagues will be so excited to move forward on our project.

Ashley

Genevieve P.

No problem at all! Glad I could help.

agp

https://community.smartsheet.com/discussion/comment/271651#Comment_271651

@Lauren Kleitz thank you for posting this! My use case was a little different than the OP's and this is just what I needed for my challenge!