Cross sheet reference to bring back value if another criteria matches
I have two sheets and would like the value of one cell to populate in the other sheet if a different cell matches with same column name in both sheets. I am fairly new to formulas and have tried INDEX/COLLECT and INDEX/MATCH and still keep getting error of #INVALID REF.
Answers
-
Either could work so you may need to post your formula for folks to help debug.
INDEX/MATCH is likely your best bet.
Use MATCH() to determine which row matches in the other sheet
Use INDEX() to get the value that you want to return that corresponds to the row that the MATCH function determined.
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
A little more context in what I am trying to achieve. Sheet 1 is where I want Assessment ID from Sheet 2 if Event ID matches with Sheet 2. The formula I tried and wasn't the only one, but I accidentally erased the others.
=INDEX({Copy of Sheet 1 Range 2}, MATCH("{Copy of Sheet 1 Range 3}", {Event ID}, 0))
-
Oh... sorry. I misunderstood. I didn't realize you were searching for the column name. You cannot do that. The column name is inaccessible. You can neither search for it nor use it in a formula.
However you can use MATCH to find the [Event ID]@row in the column of Event IDs in the other sheet. Then you can Use INDEX to return whatever data from that sheet that you want. MATCH should look something like:
MATCH([Event ID]@row,{Column of EVENT IDs in other sheet},0)
(note that the 0 looks for an exact match)
Is this what you want to do?
Be well
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 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!