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
Best Answer
-
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.
Sincerely,
Jacob Stey
Answers
-
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.
Sincerely,
Jacob Stey
-
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?
-
=IFERROR(INDEX({Project_Approval_Date_Reference}, MATCH([Project_ID]@row, {Project_ID_Reference_Column}, 0)), "")
Should do the trick
Sincerely,
Jacob Stey
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!