Stuck trying to rollup with index, vlookup, and match

KirkHayes

Hey folks, any idea how to pull this information onto another sheet horizontally?

Basically, I am trying to consolidate some project information as seen in the "Source" screenshot.

This particular sheet has the status for multiple items for the same project, but lists the same project on multiple rows.  You can see this in the Source screen shot.

I have added a health column on the Source sheet.  I want to show the health of a Project's Item on a separate sheet (Desired). But vlookup will only return the first row containing the "project name".

Basically I want to show a "Project Name" and the corresponding Health for each Item Description as shown in the Desired screenshot.  

Currently to achieve the first consolidation of Health icons, I manually linked each horizontal cell in the Desired sheet to the corresponding cell in the Source sheet.  This takes forever and is not sustainable.

Therefore I want to search the Source for the Project Name AND the Item Description to return the HEALTH.  Would this be an IFAND combined with an Index Match?

Ezra

In the other sheet that your are trying to use a vlookup, try using this instead:

=INDEX({Project Column}, MATCH([Project]@row, {Health Column}, 0))