Crosssheet formula help required
I am using the formula from Smartsheet template Metadata sheet to capture data from intake sheet. However, one of the column doesn't seem to work, anyone has any idea what went wrong?
This is the Metadata sheet, the data in red is incorrect. I am using this formula across from "Created by" to "Description":
=INDEX({Portfolio Intake Reference}, MATCH($[Project ID]@row, {Portfolio Intake Project ID}, 0), MATCH([Created by]$1, {Portfolio Intake Header Row}, 0))
Instead of showing "Harley Sterling", it is displaying the "Financial Systems Upgrade".
This is the Intake Sheet:
Anyone has any idea?
Best Answer

It took some time to understand your formula, but it essentially references a twodimensional range with the INDEX. Project ID defines the row or vertical position, and the column or horizontal position is defined by the position of ([Created by]$1 to [Description]$1 in the {{Portfolio Intake Header Row} range.
Since the formula is not wrong, you must check if your ranges are correct. I suspect either {Portfolio Intake Reference} or {Portfolio Intake Header Row} is wrong, as your formula references the wrong horizontal position.
Alternatively, you may have had some errors when dragging the relative referencing formula horizontally.
Link to published Project MetaData Sheet
Answers

It took some time to understand your formula, but it essentially references a twodimensional range with the INDEX. Project ID defines the row or vertical position, and the column or horizontal position is defined by the position of ([Created by]$1 to [Description]$1 in the {{Portfolio Intake Header Row} range.
Since the formula is not wrong, you must check if your ranges are correct. I suspect either {Portfolio Intake Reference} or {Portfolio Intake Header Row} is wrong, as your formula references the wrong horizontal position.
Alternatively, you may have had some errors when dragging the relative referencing formula horizontally.
Link to published Project MetaData Sheet

@jmyzk_cloudsmart_jp I have checked my formula, it is same as yours. The formula works from Project Name to Project Owner, but somehow is not pulling the right information for Created by.
If I follow your Portfolio Intake Reference, the entire reference is wrong. as follows:

Strange...?
I could recreate your problem by copying & pasting the Project Name columns formula to the Created By column.
I dragged the Project Name columns formula to the Created By column with the one working correctly.

@jmyzk_cloudsmart_jp strange indeed.
I did the formula at "created by" column and drag it across the rest. Truly strange that all the rest are capturing the right data except "created by". However I don't know what I did yesterday and it is now showing the right data from Created by to Project Owner.
Anyway, thanks for assisting to find out what could go wrong. Appreciate your time and effort.

@Vivien Chong Sometimes these quirks happen. Glad to hear it's working correctly now! 😁
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!