Skip to content
The Chat Archive
gpt-3.5-turbo-0125· OpenAI WildChat-4.8M (AI2)

Is there an excel formula that can do the following: If the value in column A and the…

  1. User

    Is there an excel formula that can do the following:

    If the value in column A and the value in the same row of column E in sheet QUOTES exist on the same row of sheet PORDER in this order, A value in QUOTES = A value in PORDER and E value in Quotes = C value in PORDER, and G value <> "" on the same row in PORDER then the value in column N in sheets QUOTES of the same row where the corresponding values of A and E were the search values, should be appended with the value "Completed"

  2. Assistant

    You can achieve this using an array formula in Excel. Here's a step-by-step guide to create this formula:

    1. In the N column of your QUOTES sheet, where you want the "Completed" text to appear, enter the following formula:

    =IF(COUNTIFS(PORDER!A:A, A2, PORDER!C:C, E2, PORDER!G:G, "<>") > 0, "Completed", "")

    This formula uses the COUNTIFS function to check if the conditions you specified are met in the PORDER sheet. If the conditions are met, it will return "Completed", otherwise, it will return an empty string.

    1. Press Ctrl + Shift + Enter to enter the formula as an array formula. This will apply the formula to the entire column.

    2. The formula will automatically check each row in the QUOTES sheet against the corresponding row in the PORDER sheet and append "Completed" to the N column if the conditions are met.

    3. You can adjust the formula references (A:A, C:C, G:G) and cell references (A2, E2) as needed to match your specific data layout.

    This formula will dynamically update based on changes in the QUOTES and PORDER sheets.