Array Formula vs Index/match

[3G]

Hello-

The following formula works fine, but I'm wondering if there's more than one way to skin a cat*:

=INDEX(Project_Name,MATCH(MasterLOE062911!A6,ProjectNumber,0),MATCH(MasterLOE062911!$B$5,'Master Project Roadmap'!$B$2,0))


This formula looks at the Master Project Roadmap document, and, selects the corresponding name of the projet after matching the project number on the Master LOE. However, I'm wondering if there's a way to get the same results, only using an array formula? Any thoughts?


The Master Project Roadmap has only 1 row per project and 1 name per project. The Master LOE might have multiple rows, but, the same project name & number (identical to the one on the Project Roadmap.) This need was born out of the fact that our project names continually change, but, the numbers stay the same. So, rather than have to manually change it each time on the LOE document, I can now just change it once on the Rodamap document, and, the matches will go through.


Thanks

3G


*no cats were harmed in the writing of this post.

 

[prasaddn]

Hi 3G,


I guess you would have got solution for your above question long back. I am trying to attempt this as a benefit for other readers (me).


my understanding - whenever you change project name in "Master Project Roadmap" table, the same should change in all rows of "Master LOE" Table, based on project Number as key.


Two Approach:

1. If Project Name on RIGHT side of Project Number in "Master Project Roadmap" table: simple use VLookup formula in "Master LOE" Table for Project Name column.


2. If Project Name on LEFT side of Project Number in "Master Project Roadmap" table:

Your approach of index and match is correct. You can substitute the second parameter match with exact value. See this link 1st example:

http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/


Regards,

Prasad DN