Sep 27 2018 09:41 AM
If I have 2 columns. In column 2, a value may repeat multiple times, ex. "1234" is found 5 or 6 times, doesn't matter. I want to know if column 1 has value "ABCD" and "EFGH" on the same rows as "1234" exists in column 2.
A more accurate example would be column 2 has Producer A. I want to know if Producer A has, in column 1, "Cars" and on a different line "Trucks".
Things to note"
- The table cannot be rearranged.
- The table is always changing in length, sometimes 5 rows, sometimes 1000
- I would settle with highlighted rows, auto filter, etc.
Please forgive if I'm not clear enough.
Sep 27 2018 10:47 AM - edited Sep 27 2018 10:48 AM
Hi Mark,
Please try this formula and find it in the attached workbook:
=IF(AND(SUMPRODUCT(ISNUMBER(MATCH(E2:E3,IF(D2=B2:B9,A2:A9),0))+0)=COUNTA(E2:E3),COUNTA(E2:E3)<>0),"Yes","No")
The formula in the above screenshot is located in cell F2 and it gives you (No) as a result because the Producer A has no corresponding value called (Buses)!
But if you change the value of (Buses) in cell E3 to (Trucks), the formula will return (Yes).
Please note that the formula is an array formula so you have to press Ctrl+Shift+Enter to enter this formula each time you open it in the edit mode in order to get the correct result.
Hope that helps
Regards