Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash· GoogleHugging Face LMArena Human Preference 140K

Write me an Excel 2016 formula that uses data in column D of a sheet called ChannelsALL…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Geminigemini-2.5-pro’s answer.
  1. User

    Write me an Excel 2016 formula that uses data in column D of a sheet called Channels_ALL to find its matching data in column E of another sheet called Sheet1. Once a match has been found, the corresponding data in column L of Sheet1 needs to be populated in column F of Channels_ALL. For example, Column D of Channels_ALL has "(4: 1: 0)," its corresponding column E of Sheet1 has "P:4:01:0". The data in column L of Sheet1 is "2.07.25". Therefore, 2.07.25 needs to be populated in column F of Channels_ALL.

  2. Gemini Assistant

    This is a common Excel challenge when data isn't perfectly formatted for direct lookups. The key issue here is that the format of the data in Channels_ALL!D (4: 1: 0) is different from Sheet1!E P:4:01:0. Specifically: 1. Channels_ALL!D has parentheses () and spaces . 2. Sheet1!E has a P: prefix and a leading zero for the middle number (01 vs 1).

    To get a match, we need to transform both strings into a consistent, normalized format (e.g., 4:1:0).

    Given Excel 2016, the most robust and maintainable way to do this is often with helper columns. This breaks down the complex string manipulation into smaller, manageable steps.


    Recommended Solution: Using Helper Columns (Most Robust & Easiest to Debug)

    This approach involves adding one helper column on each sheet to normalize the data before performing the lookup.

    Step 1: On Channels_ALL sheet, create a normalized lookup key.

    1. Insert a new column, let's say Column G, next to Column D (or anywhere suitable).
    2. In cell G2 (assuming your data starts in row 2), enter the following formula:
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ","")
      
      • This formula removes ( ), and spaces from the string in Column D.
      • For (4: 1: 0), this will result in 4:1:0.
    3. Drag the fill handle (the small square at the bottom-right of cell G2) down to apply this formula to all relevant rows in Column G.

    Step 2: On Sheet1, create a normalized lookup key.

    1. Insert a new column, let's say Column M, next to Column E (or anywhere suitable).
    2. In cell M2 (assuming your data starts in row 2), enter the following formula:
      =LEFT(SUBSTITUTE(E2,"P:",""),FIND(":",SUBSTITUTE(E2,"P:",""))-1) & ":" & VALUE(MID(SUBSTITUTE(E2,"P:",""),FIND(":",SUBSTITUTE(E2,"P:",""))+1,FIND(":",SUBSTITUTE(E2,"P:",""),FIND(":",SUBSTITUTE(E2,"P:",""))+1)-(FIND(":",SUBSTITUTE(E2,"P:",""))+1))) & ":" & RIGHT(SUBSTITUTE(E2,"P:",""),LEN(SUBSTITUTE(E2,"P:",""))-FIND(":",SUBSTITUTE(E2,"P:",""),FIND(":",SUBSTITUTE(E2,"P:",""))+1))
      
      • This formula is more complex, but it performs the following:
        • SUBSTITUTE(E2,"P:",""): Removes the P: prefix (e.g., P:4:01:0 becomes 4:01:0).
        • Then it parses the string into three parts (X, Y, Z) using LEFT, MID, RIGHT, and FIND for delimiters.
        • Crucially, VALUE() is applied to the middle part (01) to convert it to a number, which automatically removes the leading zero (1).
        • Finally, the three parts are re-concatenated with colons (:) to form 4:1:0.
    3. Drag the fill handle down to apply this formula to all relevant rows in Column M.

    Step 3: On Channels_ALL sheet, populate Column F using VLOOKUP or INDEX MATCH.

    1. In cell F2 on the Channels_ALL sheet, enter either of these formulas:

      Using VLOOKUP (if helper column M is to the left of L on Sheet1):

      =VLOOKUP(G2,Sheet1!M:L,2,FALSE)
      
      • G2: The normalized value from Channels_ALL.
      • Sheet1!M:L: The lookup range on Sheet1, starting with your new helper column (M) and extending to Column L.
      • 2: Indicates that you want to return the value from the 2nd column of the specified range (M is 1, L is 2).
      • FALSE: Ensures an exact match.

      Using INDEX/MATCH (more flexible, recommended if column order changes):

      =INDEX(Sheet1!L:L,MATCH(G2,Sheet1!M:M,0))
      
      • Sheet1!L:L: The column you want to return the value from.
      • G2: The normalized lookup value from Channels_ALL.
      • Sheet1!M:M: The column containing the normalized lookup keys on Sheet1.
      • 0: Ensures an exact match.
    2. Drag the fill handle down to apply the formula to all relevant rows in Column F.


    Example Walkthrough:

    Let's assume:

    • Channels_ALL!D2 contains (4: 1: 0)
    • Sheet1!E5 contains P:4:01:0
    • Sheet1!L5 contains 2.07.25
    1. Channels_ALL!G2 will become =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ","") which evaluates to 4:1:0.
    2. Sheet1!M5 will become =LEFT(SUBSTITUTE(E5,"P:",""),FIND(":",SUBSTITUTE(E5,"P:",""))-1) & ":" & VALUE(MID(SUBSTITUTE(E5,"P:",""),FIND(":",SUBSTITUTE(E5,"P:",""))+1,FIND(":",SUBSTITUTE(E5,"P:",""),FIND(":",SUBSTITUTE(E5,"P:",""))+1)-(FIND(":",SUBSTITUTE(E5,"P:",""))+1))) & ":" & RIGHT(SUBSTITUTE(E5,"P:",""),LEN(SUBSTITUTE(E5,"P:",""))-FIND(":",SUBSTITUTE(E5,"P:",""),FIND(":",SUBSTITUTE(E5,"P:",""))+1)) which evaluates to 4:1:0.
    3. Channels_ALL!F2 using INDEX/MATCH will be =INDEX(Sheet1!L:L,MATCH(G2,Sheet1!M:M,0)).
      • MATCH(G2,Sheet1!M:M,0) becomes MATCH("4:1:0",Sheet1!M:M,0). If 4:1:0 is found in Sheet1!M5, it returns 5.
      • INDEX(Sheet1!L:L,5) then returns the value from Sheet1!L5, which is 2.07.25.

    This 2.07.25 will then be populated into Channels_ALL!F2.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.