Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

#Invalid Data Type with VLOOKUP

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

  • Employee
    Answer ✓

    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

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

Answers

  • Employee
    Answer ✓

    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

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

  • 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!

Trending in Formulas and Functions