INDEX/MATCH for Mirroring Status Column in Another Sheet

bricolage

Hello!

This may not be right formula, but if a user shared a sheet with me which contains a project codes column and a status column (complete/incomplete), and I use the same project codes, can I do a formula which pulls in their status into my sheet? Something like -

If (External Referenced Sheet / Column A) matches project code in (My Sheet / Column A),

then add the value in (External Referenced Sheet / Column B) from (External Reference Sheet / Column B) intro (My Sheet / Column B).

Thank you!

Amanda Alv

Hi there,

You can definitely do that using Sheet Reference. The formula below checks Column A of your External sheet and when it matches the data in your Column A, it will enter the data from Column B of your External sheet into Column B of your sheet.

This formula should be placed in Column B of your sheet, and you will have to manually enter the sheet reference. Highlight the full {Reference Sheet.....} and then select "Reference Another Sheet". At that point you will select the correct sheet and then the header column of Column B, or Column A based on which one you are updating in the formula. If you don't highlight the {} as well, you'll notice that every time you edit a reference, it adds more of those brackets, which messes up your reference. Just make sure there is only one set of {} around your reference sheet.

=IFERROR(INDEX({Reference Sheet Column B}, MATCH([Column A]@row, {Reference sheet Column A}, 0)), "")

Let me know if you have any questions!

Amanda Alv

Hi there,

You can definitely do that using Sheet Reference. The formula below checks Column A of your External sheet and when it matches the data in your Column A, it will enter the data from Column B of your External sheet into Column B of your sheet.

This formula should be placed in Column B of your sheet, and you will have to manually enter the sheet reference. Highlight the full {Reference Sheet.....} and then select "Reference Another Sheet". At that point you will select the correct sheet and then the header column of Column B, or Column A based on which one you are updating in the formula. If you don't highlight the {} as well, you'll notice that every time you edit a reference, it adds more of those brackets, which messes up your reference. Just make sure there is only one set of {} around your reference sheet.

=IFERROR(INDEX({Reference Sheet Column B}, MATCH([Column A]@row, {Reference sheet Column A}, 0)), "")

Let me know if you have any questions!

bricolage

That's so much Amanda. It worked!

Can this formula be adapted so that instead of just 'Column A' being the match, it is 'Column A' and 'COLUMN C'. So that if BOTH match then Column B is updated.

It seems like the below (using COLLECT) may help. But I am not sure how to adapt it to this formula.

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

Amanda Alv

So glad to hear it!

And yes, you can certainly add in your additional criteria with COLLECT, but I used JOIN. I tested it out to make sure the alignment

=JOIN(COLLECT({Reference Sheet Column B}, {Ref Sheet Column A}, [Column A]@row, {Ref Sheet Column C}, [Column C]@row))

Let me know if you have any other questions!