Index/Match instead Vlookup

Hi good day. I just created a formula to grab dates from another source sheet. The situation here that i created using VLOOKUP and read that it is recommendable to use Index/Match for this kind of situations. Can you help me to build the formula and including if there is #NO MATCH show in blank? Thanks in advance. Below the formula that i created and works.

=VLOOKUP([Project_ID]@row, {Capital Planning 2023 - Intake Submissi... Range 1}, 23, false)

Capital planning is the source sheet were i need to grab the date in column 23. The column name in the source is Project_Approval_Date

Find more posts tagged with

Formulas

Accepted answers

SteyJ

hi @Raul Cabrera

try this

=INDEX({Project_Approval_Date_Reference}, MATCH([Project_ID]@row, {Project_ID_Reference_Column}, 0))

The first bold is a reference to the column for "Project_Approval_Date", the 2nd bold is a reference to the Project ID column.

Now, when you enter the Project ID in a row, the index/match formula column will show the Date which is desired.

Does this work? Let me know.

All comments

SteyJ

hi @Raul Cabrera

try this

=INDEX({Project_Approval_Date_Reference}, MATCH([Project_ID]@row, {Project_ID_Reference_Column}, 0))

The first bold is a reference to the column for "Project_Approval_Date", the 2nd bold is a reference to the Project ID column.

Now, when you enter the Project ID in a row, the index/match formula column will show the Date which is desired.

Does this work? Let me know.

Raul Cabrera

@SteyJ

Thanks. Now is working. Just one more question related. How can i add the condition to eliminate the #NOMATCH, meaning if that is the result, show the column in blank?

SteyJ

https://community.smartsheet.com/discussion/comment/394773#Comment_394773

=IFERROR(INDEX({Project_Approval_Date_Reference}, MATCH([Project_ID]@row, {Project_ID_Reference_Column}, 0)), "")

Should do the trick

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.