I have 2 arrays. The first array lists account nr's with the company name and a column that tracks if they were active last month (marked with an x). The second array has two column; one with only company names and another that needs to be marked with an x if the company is present in the first array in January.
Objective: I want to track active companies in January. To do this, I want to mark the second column in array 2 for all companies that have an 'x' in 'active Jan' in array 1.
Array 1
| Name Company[1] | account nr. | active Jan |
| ------------ | ------------ | ------------ |
| A | 123 | x |
| B | 321 | |
| B | 132 | x |
Array 2
| Name Company[2] | active Jan |
| ------------ | ------------ |
| A | |
| B | |
What I tried: formula in [array 2,column 2] that gives the value in [array1,column 3] based on a match between name company in array 1 and array 2:
=IFERROR(VLOOKUP([@NameCompany[2]],'array1'!A3:C5,3,FALSE),"")
Result: This gives the first blank or x values of a company in array1. I need to only give an 'x' in [array2,column2] if there is an x in ]array1,column3], instead of copying the first value in [array1,column3] the formula comes across. For example, the formula above would give a 0 value (or blank) for company B instead of an x because there are multiple accounts from company B. Ideally, the formula should search only companies with an 'x' in [array1,column3] and then put an 'x' in [array2,column2].
I know I use the wrong formula to reach my objective but I can't find the right one in google/stackoverflow. Please help.