Cross sheet reference to bring back value if another criteria matches

ASchindl

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.

Scott Orsey

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.

ASchindl

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))

Scott Orsey

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