Forum Discussion

nick92's avatar
nick92
Copper Contributor
Sep 04, 2023

Lookup to return TRUE or FALSE

Hi All,
I'm hoping somebody will have a solution to a problem that I have - i'm really struggling!


I have a list of 200,000 rows of data that shows if one of our stores range a certain product.
If the Store Number and Product Number are not present then the line is not ranged in that store.
Column A has the Store Number and Column B has the product number as below:

 

I'm wanting to build this data into a third-party tool that we currently use to range these products for sale online.
We currently have a sheet that we export to a CSV and then upload to the third-party.
Column A displays either TRUE or FALSE and this determines if the product will show on the website or not.

 

I want to be able to create an individual sheet for each store so that Column A will show TRUE or FALSE dependent on if the product is ranged in that store or not (using the range list).


I would just use a helper column to return the TRUE or FALSE however i'm unable to do this as this extra column would break our CSV file upload tool. I will therefore need to use a single formula in

 

Column A that would lookup to check both the Store Number e.g. 102 and Product Number e.g. 10757 were present in the same row and if they were then TRUE would be displayed otherwise it would show FALSE.


I hope i've explained this well. Any ideas greatly appreciated?


Thanks,
Nick

Excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 06, 2023

    nick92 

    To achieve this, you can use a combination of Excel functions like COUNTIFS or SUMPRODUCT to check if both the Store Number and Product Number are present in the same row of your data. If they are, it will return TRUE; otherwise, it will return FALSE.

    Assuming your data is in columns A (Store Number) and B (Product Number), and you want the TRUE or FALSE result in column C, you can use the following formula in cell C1:

    =IF(COUNTIFS(A:A, A1, B:B, B1) > 0, TRUE, FALSE)

    Or using SUMPRODUCT:

    =IF(SUMPRODUCT((A:A=A1)*(B:B=B1))>0,TRUE,FALSE)

    Drag this formula down for all the rows in your data. It will check if the combination of Store Number and Product Number exists in the same row and return TRUE if they do, and FALSE if they do not.

    This formula does not require a helper column and should give you the desired TRUE or FALSE result in column C based on the presence of both Store Number and Product Number in the same row of your data.The text and steps were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark them as helpful!

    This will help all forum participants.

Share

Resources