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.

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