Look-Up ??

[carlos]

I've struggled with this so hope you can help...

I am trying to create a new Table A from an existing one,
Table B.

Table B. Column A = Products, Column B = Sales Region.
The table is recording if a Sales Region has sold a
Product. For example, if ProductA was sold by Region1,
Region2, and Region3, there would be 3 rows like so:


ProductA Region1
ProductA Region2
ProductA Region3

I have set up Table A (the new one) to look like so:

Region1 Region2 Region3 Region4

ProductA
ProductB
ProductC

I would like a formula that will evalute true if the
region sold the product - like so:

Region1 Region2 Region3 Region4

ProductA TRUE TRUE TRUE
ProductB
ProductC

Thanks in advance for your guidance.

 

[Bob Umlas]

If the first ProductA is in A1 of table B, and Table A has "Region1" in E1
ad "ProductA" in D2, then ctrl/shift/enter this formula in E2:
=NOT(ISERROR(MATCH($D2&E$1,$A$1:$A$4&$B$1:$B$4,0)))
then fill right & down as necessary.

 

[Jim]

Try:
=SUMPRODUCT((Region"A")*(Product="A"))