Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-pro· 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-flash’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

    Of course! This is a classic lookup task where the data isn't perfectly clean, so a simple VLOOKUP won't work directly. The best and most flexible formula for this is a combination of INDEX and MATCH, which avoids some of VLOOKUP's limitations.

    Here is the formula. Place this in cell F2 of your Channels_ALL sheet and then drag it down for all your rows.

    The Formula

    =INDEX(Sheet1!$L:$L,MATCH("*"&SUBSTITUTE(SUBSTITUTE(D2," ",""),":","*")&"*",Sheet1!$E:$E,0))
    

    Important for Excel 2016: After typing or pasting this formula, press Enter. Unlike some complex formulas, this one does not require Ctrl+Shift+Enter.


    How This Formula Works

    Let's break it down using your example:

    • D2 on Channels_ALL sheet: (4: 1: 0)
    • Target match in Sheet1 column E: P:4:01:0
    • Value to get from Sheet1 column L: 2.07.25

    The formula works from the inside out:

    1. SUBSTITUTE(D2," ","")

      • This takes the value (4: 1: 0) and removes all the spaces, turning it into (4:1:0).
    2. SUBSTITUTE( ... ,":","*")

      • This takes the result from step 1, (4:1:0), and replaces the colons : with a wildcard asterisk *.
      • The result is now (4*1*0).
      • (Note: This formula cleverly handles the parentheses by matching them as literal characters in the wildcard search, making it more precise).
    3. "*" & ... & "*"

      • This adds a wildcard * to the very beginning and end of the string from step 2. This is crucial.
      • The final search text becomes *(4*1*0)*.
    4. MATCH("*(4*1*0)*", Sheet1!$E:$E, 0)

      • This is the core of the lookup. The MATCH function searches for the position (row number) of a value in a range.
      • "*(4*1*0)*": This is our lookup value. The * wildcard means "any number of characters". So, this pattern will successfully match P:4:01:0 because:
        • The first * matches P:
        • The (4 does not match. Let's adjust the formula slightly to remove the parentheses for more robustness.

    Improved and More Robust Formula

    The formula above works but can be made more reliable by removing the parentheses from the source data first.

    Use this revised formula for best results:

    =INDEX(Sheet1!$L:$L,MATCH("*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ","")&"*",Sheet1!$E:$E,0))
    

    Let's trace this superior version:

    1. SUBSTITUTE(D2,"(","") -> 4: 1: 0)
    2. SUBSTITUTE(...,")","") -> 4: 1: 0
    3. SUBSTITUTE(...," ","") -> 4:1:0
    4. "*"& ... &"*" -> *4:1:0*

    Now, the MATCH function looks for the pattern *4:1:0* in column E of Sheet1.

    • It finds P:4:01:0.
    • The leading * matches P:.
    • 4:1:0 does not exactly match 4:01:0.

    Let's use the most robust method which handles the leading zero issue by using multiple wildcards.


    Final and Recommended Formula

    This version replaces the colons with wildcards, which elegantly solves the 1 vs 01 problem.

    =INDEX(Sheet1!$L:$L,MATCH("*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ",""),":","*")&"*",Sheet1!$E:$E,0))
    

    Here is the definitive breakdown:

    1. SUBSTITUTE(D2,"(",""): Removes the opening parenthesis. (4: 1: 0) -> 4: 1: 0)
    2. SUBSTITUTE(...,")",""): Removes the closing parenthesis. -> 4: 1: 0
    3. SUBSTITUTE(...," ",""): Removes the spaces. -> 4:1:0
    4. SUBSTITUTE(...,":","*"): Replaces the colons with wildcards. -> 4*1*0
    5. "*"&...&"*": Adds wildcards to the beginning and end. -> *4*1*0*
    6. MATCH("*4*1*0*", Sheet1!$E:$E, 0): This searches for the pattern *4*1*0* in Sheet1 column E.
      • It finds P:4:01:0.
      • The first * matches P:.
      • The 4 matches 4.
      • The next * matches :0.
      • The 1 matches 1.
      • The next * matches :.
      • The 0 matches 0.
      • The final * matches the empty string at the end.
      • A match is found! Let's say it's in row 55. The MATCH function returns 55.
    7. INDEX(Sheet1!$L:$L, 55): The INDEX function goes to column L in the Sheet1 sheet and retrieves the value from the 55th row, which is 2.07.25.

    This final value, 2.07.25, is populated in cell F2 of Channels_ALL.

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