Having trouble using VLOOKUP formula in my Smartsheet

Helen Percy

Hello Community,

I have been trying to use VLOOKUP to cross check a screening date to what screening number was performed on that date, or within the date range; though I haven't been able to make it work! Please can you help me with what I need to do to make it work. My formula is:

=VLOOKUP([Date of stock switchover]25, {MOA1 Screening weeks Range}, 1, false)

This is a snip of the Screening weeks Smartsheet used for {MOA1 Screening weeks Range}:

And here is a snip of the main Smartsheet the formula is in:

When the User enters the date in the 'stock was switched over' column, the Screen Number found from within the MOA1 Screening weeks Range is what I would like to be automatically filled in via the formula.

Your help will be greatly appreciated.

Many thanks,

Helen

Kelly Moore

Hi Helen

The VLOOKUP formula requires that the Lookup value (in your case the [Date of stock switchover]@row) be in the leftmost column of the range you are searching. It looks like your range begins with the screen size, not the Date of switchover. To use the VLOOKUP function, you need to move the [Screen dates -2020] column to the left of the [Screen Number] column. Verify the {MOA1 Screening weeks Range} is still encompassing those same two columns. You can find more about the vlookup function here

Your formula would then be

=VLOOKUP([Date of stock switchover]@row, {MOA1 Screening weeks Range}, 2, false)

If you didn't want to reorder your columns, you could use the Index/Match function. This is my personal favorite as the order of columns isn't a constraint in this formula.

=INDEX({Screen Number},MATCH([Date of stock switchover]@row,{Screen Dates - 2020},0)

In this case you will need two ranges on your cross-referenced sheet: the column for Screen Number and a separate range for the column of your Screen Dates - 2020. The zero in the formula part of the Match function and is saying the data is unsorted. This post may be helpful to you

Let me know if I misunderstood and I'll try again

Kelly Moore

Hi Helen

The VLOOKUP formula requires that the Lookup value (in your case the [Date of stock switchover]@row) be in the leftmost column of the range you are searching. It looks like your range begins with the screen size, not the Date of switchover. To use the VLOOKUP function, you need to move the [Screen dates -2020] column to the left of the [Screen Number] column. Verify the {MOA1 Screening weeks Range} is still encompassing those same two columns. You can find more about the vlookup function here

Your formula would then be

=VLOOKUP([Date of stock switchover]@row, {MOA1 Screening weeks Range}, 2, false)

If you didn't want to reorder your columns, you could use the Index/Match function. This is my personal favorite as the order of columns isn't a constraint in this formula.

=INDEX({Screen Number},MATCH([Date of stock switchover]@row,{Screen Dates - 2020},0)

In this case you will need two ranges on your cross-referenced sheet: the column for Screen Number and a separate range for the column of your Screen Dates - 2020. The zero in the formula part of the Match function and is saying the data is unsorted. This post may be helpful to you

Let me know if I misunderstood and I'll try again

Helen Percy

Thank you very much KDM for your comment and solution 😁 It worked 👍️