IF statement VLOOKUP reference to other sheets

Hello,

I am looking to create a VLOOKUP reference to other sheets based on a date column. I think I am pretty close but still getting #INVALID OPERATION

=IF([Date Agreed]@row = >31 / 11 / 2023, (VLOOKUP(Neutral@row, {2022/23 Mediator List Range 1}, 6, false)), (VLOOKUP(Neutral@row, {Copy of 2022/23 Mediator List Range 1}, 6, false)))

I can't see any problems with either the IF statement or the VLOOKUP!

Help please!

Karen

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 01/10/24 Answer ✓

    @klangridge103846,

    There is a slight issue: November only has 30 days in it so 31/11/2023 isn't a valid date! Try amending to

    =IF([Date Agreed]@row >= DATE(2023, 11, 30), (VLOOKUP(Neutral@row, {2022/23 Mediator List Range 1}, 6, false)), (VLOOKUP(Neutral@row, {Copy of 2022/23 Mediator List Range 1}, 6, false)))

    This should get rid of the invalid operation - if there are any problems due to the VLOOKUPS etc. then just post!

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 01/10/24 Answer ✓

    @klangridge103846,

    There is a slight issue: November only has 30 days in it so 31/11/2023 isn't a valid date! Try amending to

    =IF([Date Agreed]@row >= DATE(2023, 11, 30), (VLOOKUP(Neutral@row, {2022/23 Mediator List Range 1}, 6, false)), (VLOOKUP(Neutral@row, {Copy of 2022/23 Mediator List Range 1}, 6, false)))

    This should get rid of the invalid operation - if there are any problems due to the VLOOKUPS etc. then just post!

  • You are an angel! I feel very silly now! Thank you so much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!