Finding if a value is located in a range of cells in a corresponding column, to a row with the same value as a given cell in a given row

Question

Given that column A contains directory paths, and column B contains actions on these paths; I'd like a resultant cell to contain a specific string if a specific string is located in Column B (per column A).

Granted I can sort the column, I believe I can use OFFSET, but would like to know how to locate an ending index of cells so I can search the range returned by OFFSET.

For example:

Path                Operation
/share/Admins       Accessed
/share/Admins       Removed
/share/Admins       Added
/share/Admins       Changed
/share/Network      Accessed
/shared/Projects    Accessed

In this case, I want to search Path for a unique value (in this case /share/Admins, /share/Network, and /shared/Projects), and given this range in Path, I'd like to search the corresponding Operation, and if any Operation that matches Removed, Added, Changed exists, I'd like the cell value to be WRITE; and if those values aren't found, "READ".

In this case, I would expect the column (with header Result) to read:

Path                Operation       Result
/share/Admins       Accessed        WRITE
/share/Admins       Removed         WRITE
/share/Admins       Added           WRITE
/share/Admins       Changed         WRITE
/share/Network      Accessed        READ
/shared/Projects    Accessed        READ

Pardon the SEO: I am using this to compare Varonis DatAdvantage reports 01.a.01 (user access logs) with 04.j.01 (effective user permissions report). Unfortunately, DatAdvantage doesn't feel the need to write reports that correlate their user activity records and file system permissions records.

Answer 1

You can use Sumproduct. See screenshot.

=IF(SUMPRODUCT((A:A=A1)*((B:B="Removed")+(B:B="Added")+(B:B="Changed"))),"WRITE","READ")

Or in shorter form:

=IF(SUMPRODUCT((A:A=A1)*(B:B={"Removed","Added","Changed"})),"WRITE","READ")