Find a specific number pattern in a column

p45cal · #5 02-12-2020, 01:31 PM

Without the helper column but needing to be array-entered (with Ctrl+Shift+Enter, not just Enter):

Code:

=IF(P2="","",HYPERLINK("#'File'!E" & MATCH(M2 & "|" & N2 & "|" & O2 & "|" & P2,$E$1:$E$900 & "|" & $E$2:$E$901 & "|" & E3:$E$902 & "|" & $E$4:$E$903,0),"Find"))

Note that I've used the likes of:

Code:

M2 & "|" & N2 & "|" & O2 & "|" & P2

instead of:

Code:

M2&N2&O2&P2

only to be more robust because:
2,32,32,32
and
23,2,2,232
both concatenate to the same
2323232
Using delimiters they're different:
2|32|32|32
23|2|2|232
so you won't get false finds.

See attached where I have engineered such a false find.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formula to check combinations of values in one column to find match from another column	kong1802	Excel	1	06-15-2018 05:26 AM
Find and select all string of simlar pattern	anon123	Word	4	04-20-2016 11:41 PM
Extract data based on pattern with respect to specific column	PRA007	Excel Programming	14	12-04-2015 04:32 AM
Want a quotient using a constant to show up in column c each time a number is added to column b	fibbermcghee	Excel	2	12-09-2014 05:48 PM
Find & Replace: Wildcards (except this pattern)	tinfanide	Word	6	01-26-2014 06:39 AM