#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.

Find more posts tagged with

Sheets

Formulas

Accepted answers

Genevieve P.

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

All comments

Genevieve P.

Hi @Yahaira Paredes

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

It worked!

Thank you very much for your help Genevieve!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.