#Invalid Data Type with VLOOKUP

Options

Good day everyone,

I'm trying to use the formula VLOOKUP to pull some information from another sheets, but it shows me #Invalid Data Type.


I have this sheet named "2020 vs 2021 P/Month" and I need the formula to look for revenue on sheet "Rev By Month P/BTC 2020", so when I fill this last one, fills up the information to the sheet "2020 vs 2021 P/Month".

This is the formula that I'm trying to use according an article named "Reference Data from Another Sheet":

=VLOOKUP([Bill To Code]@row, {Rev By Month P/BTC 2020 Range 2}, {Rev By Month P/BTC 2020 Range 3})

I don't know if I'm missing some step, any help will be greatly appreciated.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Yahaira Paredes

    The VLOOKUP function requires a range to be selected (so a number of columns, starting with the first column with your matching value and then containing your other column with the value to return). However I see that you have a number of hidden columns between the two you're searching for.

    I would suggest using an INDEX(MATCH function instead! This allows you to reference each column individually instead of selecting a range.

    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))

    Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Yahaira Paredes

    The VLOOKUP function requires a range to be selected (so a number of columns, starting with the first column with your matching value and then containing your other column with the value to return). However I see that you have a number of hidden columns between the two you're searching for.

    I would suggest using an INDEX(MATCH function instead! This allows you to reference each column individually instead of selecting a range.

    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))

    Let me know if this works for you!

    Cheers,

    Genevieve

  • Yahaira Paredes
    Options

    It worked!

    Thank you very much for your help Genevieve!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!