Index/Match instead Vlookup

Raul Cabrera · 09/08/23

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

SteyJ · 09/09/23

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.

SteyJ · 09/09/23

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 · 09/11/23

@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 · 09/11/23

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

Index/Match instead Vlookup

Best Answer

Sincerely,

Answers

Sincerely,

Sincerely,

Help Article Resources

Categories