Write me an Excel 2016 formula that uses data in column D of a sheet called ChannelsALL…
- 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.
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 fromSheet1!EP:4:01:0. Specifically: 1.Channels_ALL!Dhas parentheses()and spaces. 2.Sheet1!Ehas aP:prefix and a leading zero for the middle number (01vs1).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_ALLsheet, create a normalized lookup key.- Insert a new column, let's say Column
G, next to ColumnD(or anywhere suitable). - In cell
G2(assuming your data starts in row 2), enter the following formula:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ","")- This formula removes
(), and spacesfrom the string in Column D. - For
(4: 1: 0), this will result in4:1:0.
- This formula removes
- 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.- Insert a new column, let's say Column
M, next to ColumnE(or anywhere suitable). - 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 theP:prefix (e.g.,P:4:01:0becomes4:01:0).- Then it parses the string into three parts (X, Y, Z) using
LEFT,MID,RIGHT, andFINDfor 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 form4:1:0.
- This formula is more complex, but it performs the following:
- Drag the fill handle down to apply this formula to all relevant rows in Column M.
Step 3: On
Channels_ALLsheet, populate Column F usingVLOOKUPorINDEX MATCH.-
In cell
F2on theChannels_ALLsheet, enter either of these formulas:Using
VLOOKUP(if helper columnMis to the left ofLon Sheet1):=VLOOKUP(G2,Sheet1!M:L,2,FALSE)G2: The normalized value fromChannels_ALL.Sheet1!M:L: The lookup range onSheet1, starting with your new helper column (M) and extending to ColumnL.2: Indicates that you want to return the value from the 2nd column of the specified range (Mis 1,Lis 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 fromChannels_ALL.Sheet1!M:M: The column containing the normalized lookup keys onSheet1.0: Ensures an exact match.
-
Drag the fill handle down to apply the formula to all relevant rows in Column F.
Example Walkthrough:
Let's assume:
Channels_ALL!D2contains(4: 1: 0)Sheet1!E5containsP:4:01:0Sheet1!L5contains2.07.25
Channels_ALL!G2will become=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ","")which evaluates to4:1:0.Sheet1!M5will 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 to4:1:0.Channels_ALL!F2usingINDEX/MATCHwill be=INDEX(Sheet1!L:L,MATCH(G2,Sheet1!M:M,0)).MATCH(G2,Sheet1!M:M,0)becomesMATCH("4:1:0",Sheet1!M:M,0). If4:1:0is found inSheet1!M5, it returns5.INDEX(Sheet1!L:L,5)then returns the value fromSheet1!L5, which is2.07.25.
This
2.07.25will then be populated intoChannels_ALL!F2.- Insert a new column, let's say Column
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.