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
Of course! This is a classic lookup task where the data isn't perfectly clean, so a simple
VLOOKUPwon't work directly. The best and most flexible formula for this is a combination ofINDEXandMATCH, which avoids some ofVLOOKUP's limitations.Here is the formula. Place this in cell
F2of yourChannels_ALLsheet 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_ALLsheet:(4: 1: 0) - Target match in
Sheet1column E:P:4:01:0 - Value to get from
Sheet1column L:2.07.25
The formula works from the inside out:
-
SUBSTITUTE(D2," ","")- This takes the value
(4: 1: 0)and removes all the spaces, turning it into(4:1:0).
- This takes the value
-
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).
- This takes the result from step 1,
-
"*" & ... & "*"- 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)*.
- This adds a wildcard
-
MATCH("*(4*1*0)*", Sheet1!$E:$E, 0)- This is the core of the lookup. The
MATCHfunction 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 matchP:4:01:0because:- The first
*matchesP: - The
(4does not match. Let's adjust the formula slightly to remove the parentheses for more robustness.
- The first
- This is the core of the lookup. The
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:
SUBSTITUTE(D2,"(","")->4: 1: 0)SUBSTITUTE(...,")","")->4: 1: 0SUBSTITUTE(...," ","")->4:1:0"*"& ... &"*"->*4:1:0*
Now, the
MATCHfunction looks for the pattern*4:1:0*in column E ofSheet1.- It finds
P:4:01:0. - The leading
*matchesP:. 4:1:0does not exactly match4: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
1vs01problem.=INDEX(Sheet1!$L:$L,MATCH("*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ",""),":","*")&"*",Sheet1!$E:$E,0))Here is the definitive breakdown:
SUBSTITUTE(D2,"(",""): Removes the opening parenthesis.(4: 1: 0)->4: 1: 0)SUBSTITUTE(...,")",""): Removes the closing parenthesis. ->4: 1: 0SUBSTITUTE(...," ",""): Removes the spaces. ->4:1:0SUBSTITUTE(...,":","*"): Replaces the colons with wildcards. ->4*1*0"*"&...&"*": Adds wildcards to the beginning and end. ->*4*1*0*MATCH("*4*1*0*", Sheet1!$E:$E, 0): This searches for the pattern*4*1*0*inSheet1column E.- It finds
P:4:01:0. - The first
*matchesP:. - The
4matches4. - The next
*matches:0. - The
1matches1. - The next
*matches:. - The
0matches0. - The final
*matches the empty string at the end. - A match is found! Let's say it's in row 55. The
MATCHfunction returns55.
- It finds
INDEX(Sheet1!$L:$L, 55): TheINDEXfunction goes to column L in theSheet1sheet and retrieves the value from the 55th row, which is2.07.25.
This final value,
2.07.25, is populated in cellF2ofChannels_ALL.- D2 on
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.