How to check if a column has a value on a certain row with matching cell?

Hi,

I have two sheets, Allocations & Installations.

Once an Allocation is approved, the row gets copied to Installation. When the Installation is completed it is marked as such on the same sheet. I would like to automatically have it marked on Allocation as well.

They share a [Shop ID] cell. So i need to check from Allocation with a reference to Installation if it has the Shop ID and on that same row if the cell in [Completed] has a value. There are multiple 'completed' values in [Completed].

If so, I want [Completed] on Allocations to be filled in or checked for that Shop ID.

I just can't get this done with any combination of VLOOKUP, INDEX, MATCH or IF/AND functions in any way and have exhausted Google & this community by this point.

Can anyone point me in the right direction?

Find more posts tagged with

Sheets

Formulas

Accepted answers

Kelly Moore

Hey @IT Belgium

Will this work for you? The formula would go into your Allocation sheet Completed column.

=IF(COUNTIFS({Installation Shop ID}, [Allocations Shop ID]@row)>0, INDEX({Installation Completed}, MATCH([Allocation Shop ID]@row, {Installation Shop ID}, 0))

It first checks to see if the shop IDs are on both sheets, then runs the INDEX/MATCH

Remember since these are cross-referenced formulas they have to be built by you - you cannot simply copy paste into your sheet. As a good practice, rename the ranges as you create them to

Kelly

All comments

Kelly Moore

Hey @IT Belgium

Will this work for you? The formula would go into your Allocation sheet Completed column.

=IF(COUNTIFS({Installation Shop ID}, [Allocations Shop ID]@row)>0, INDEX({Installation Completed}, MATCH([Allocation Shop ID]@row, {Installation Shop ID}, 0))

It first checks to see if the shop IDs are on both sheets, then runs the INDEX/MATCH

Remember since these are cross-referenced formulas they have to be built by you - you cannot simply copy paste into your sheet. As a good practice, rename the ranges as you create them to

Kelly

IT Belgium

Hi,

That worked like a charm, thank you so much!

Seeing the solution now makes sense, just being able to come up with it in a formula is something that will take me a long time to learn.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.