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?
Best Answer
-
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
Answers
-
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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!