Return value or cell reference of Pattern Match from a Range

notruck

New Member
Joined
Jan 19, 2011
Messages
34
Hello All,

I am trying to build a formula using Index/Match but my range includes multiple columns. The range where I am trying to find a match is D2:S9500. With the example data provided below, I believe I have two ways to get my result with the best option using a simple Index/Match with a known "lookup_value" (i.e. "CP 5598") for the Match. I have already started this way but the nested IF statements are going to kill me. The other option is to use a helper formula to extract the values that match my pattern into columns A, B, C, or D and then I would only have a few nested IF statements in the primary formula.

Example values I am trying to Match are "CP 0979", "CP 1063", or "CP 0745". Sometimes there are up to 4 values per row that match that pattern.


Excel 2010
EFGHIJKL
1CP 1817 TK 002CP 1813 TK 001CP 1813 TK 003CP 1816 TK 011CP 1817 TK 098
2CP 5565 TK 029CP 5565 TK 027CP 5565 TK 008CP 5565 TK 023CP 5565 TK 002CP 5565 TK 021CP 5565CP 5565 TK 004
3CP 5074 TK 029CP 5074 TK 027CP 5074 TK 004CP 5074 TK 002CP 5073 TK 023CP 5073 TK 021CP 5074
4
5CP 6104 TK 007CP 6104 TK 005CP 6103 TK 023CP 6103 TK 021CP 6103 TK 006CP 6103 TK 004CP 6103 TK 003CP 6103 TK 002
6CP 6917 TK 1
7CP 5598 TK 002CP 5598CP 5597 TK 041CP 5597 TK 043
8CP 0981CP 0981 TK 002CP 0981 TK 005CP 0981 TK 007CP 0980 TK 001
9
10CP 3984 TK 03
11CP 0979CP 0979 TK 005CP 0979 TK 007CP 0979 TK 002CP 0979 TK 009CP 0979 TK 001
12CP 2452CP 2452 TK 005CP 2452 TK 007CP 2452 TK 002CP 2451 TK 003
13CP 0978CP 0978 TK 003CP 0978 TK 023CP 0978 TK 004CP 0979 TK 005CP 0979 TK 007
14CP 5563 TK 009CP 5563 TK 007CP 5562 TK 008CP 5562 TK 006CP 5562 TK 005CP 5562 TK 004CP 5562 TK 003CP 5562 TK 002
15CP 5087 TK 031CP 5086 TK 033CP 5086 TK 029CP 5086 TK 028CP 5086 TK 027CP 5086 TK 026CP 5086 TK 024CP 5086 TK 023
16CP 0977CP 0975 TK 015CP 0975 TK 017CP 0977 TK 002CP 0977 TK 004CP 0977 TK 006CP 0977 TK 013CP 0977 TK 003
17CP 5087 TK 031CP 5087 TK 003CP 5087 TK 002CP 5086 TK 029CP 5086 TK 027CP 5087
18CP 5564 TK 003CP 5564 TK 001CP 5563 TK 009CP 5563 TK 007CP 5563 TK 004CP 5563 TK 002CP 5562 TK 005CP 5563
19CP 5088 TK 007CP 5088 TK 005CP 5088 TK 004CP 5088 TK 002CP 5087 TK 003CP 5086 TK 027CP 5088
20
21CP 2431 TK 001
22CP 1064 TK 007
23CP 1062CP 1063CP 1063 TK 005CP 1063 TK 003CP 1063 TK 002CP 1064 TK 007*
24CP 6719 TK 53CP 6719 TK 51CP 6719 TK 29CP 6720 TK 001CP 6720 TK 005CP 6720 TK 31CP 6720 TK 33
25CP 5715 TK 003
26CP 7307 TK 13
27CP 2438 TK 009CP 2437 TK 001
28CP 8086 TK 31
29CP 8086 TK 31
30CP 9325 TK 59
31CP 0744CP 0745CP 0744 TK 013CP 0744 TK 009CP 0745 TK 017CP 0745 TK 002CP 0744 TK 002CP 0744 TK 021
32CP 5504 TK 009
33CP 2116 TK 003
34CP 6900 TK 005CP 6901 TK 011
35
36CP 7160 TK 1
37CP 3105 TK 002CP 3105 TK 001CP 3104 TK 011CP 3104 TK 009CP 3105
38CP 7377 TK 3
Sheet1
Cell Formulas
RangeFormula
E1CP 1817 TK 002
E2CP 5565 TK 029
E3CP 5074 TK 029
E5CP 6104 TK 007
E6CP 6917 TK 1
E7CP 5598 TK 002
E8CP 0981
E10CP 3984 TK 03
E11CP 0979
E12CP 2452
E13CP 0978
E14CP 5563 TK 009
E15CP 5087 TK 031
E16CP 0977
E17CP 5087 TK 031
E18CP 5564 TK 003
E19CP 5088 TK 007
E21CP 2431 TK 001
E22CP 1064 TK 007
E23CP 1062
E24CP 6719 TK 53
E25CP 5715 TK 003
E26CP 7307 TK 13
F1CP 1813 TK 001
F2CP 5565 TK 027
F3CP 5074 TK 027
F5CP 6104 TK 005
F7CP 5598
F8CP 0981 TK 002
F11CP 0979 TK 005
F12CP 2452 TK 005
F13CP 0978 TK 003
F14CP 5563 TK 007
F15CP 5086 TK 033
F16CP 0975 TK 015
F17CP 5087 TK 003
F18CP 5564 TK 001
F19CP 5088 TK 005
F23CP 1063
F24CP 6719 TK 51
G1CP 1813 TK 003
G2CP 5565 TK 008
G3CP 5074 TK 004
G5CP 6103 TK 023
G7CP 5597 TK 041
G8CP 0981 TK 005
G11CP 0979 TK 007
G12CP 2452 TK 007
G13CP 0978 TK 023
G14CP 5562 TK 008
G15CP 5086 TK 029
G16CP 0975 TK 017
G17CP 5087 TK 002
G18CP 5563 TK 009
G19CP 5088 TK 004
G23CP 1063 TK 005
G24CP 6719 TK 29
H1CP 1816 TK 011
H2CP 5565 TK 023
H3CP 5074 TK 002
H5CP 6103 TK 021
H7CP 5597 TK 043
H8CP 0981 TK 007
H11CP 0979 TK 002
H12CP 2452 TK 002
H13CP 0978 TK 004
H14CP 5562 TK 006
H15CP 5086 TK 028
H16CP 0977 TK 002
H17CP 5086 TK 029
H18CP 5563 TK 007
H19CP 5088 TK 002
H23CP 1063 TK 003
H24CP 6720 TK 001
I1CP 1817 TK 098
I2CP 5565 TK 002
I3CP 5073 TK 023
I5CP 6103 TK 006
I8CP 0980 TK 001
I11CP 0979 TK 009
I12CP 2451 TK 003
I13CP 0979 TK 005
I14CP 5562 TK 005
I15CP 5086 TK 027
I16CP 0977 TK 004
I17CP 5086 TK 027
I18CP 5563 TK 004
I19CP 5087 TK 003
I23CP 1063 TK 002
I24CP 6720 TK 005
J2CP 5565 TK 021
J3CP 5073 TK 021
J5CP 6103 TK 004
J11CP 0979 TK 001
J13CP 0979 TK 007
J14CP 5562 TK 004
J15CP 5086 TK 026
J16CP 0977 TK 006
J17CP 5087
J18CP 5563 TK 002
J19CP 5086 TK 027
J23CP 1064 TK 007*
J24CP 6720 TK 31
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I am hoping that no response doesn't mean that this is not possible. The is the formula I am using with the nested IF's although to complete what I need would require 10 more IF's.

=IF(NOT(ISNA(MATCH("CP "&B14,Sheet1!$M$2:$M$20000,0))),MATCH("CP "&B14,Sheet1!$M$2:$M$20000,0),IF(NOT(ISNA(MATCH("CP "&B14,Sheet1!$N$2:$N$20000,0))),MATCH("CP "&B14,Sheet1!$N$2:$N$20000,0),IF(NOT(ISNA(MATCH("CP "&B14,Sheet1!$O$2:$O$20000,0))),MATCH("CP "&B14,Sheet1!$O$2:$O$20000,0),IF(NOT(ISNA(MATCH("CP "&B14,Sheet1!$P$2:$P$20000,0))),MATCH("CP "&B14,Sheet1!$P$2:$P$20000,0),IF(NOT(ISNA(MATCH("CP "&B14,Sheet1!$Q$2:$Q$20000,0))),MATCH("CP "&B14,Sheet1!$Q$2:$Q$20000,0),IF(NOT(ISNA(MATCH("CP "&B14,Sheet1!$R$2:$R$20000,0))),MATCH("CP "&B14,Sheet1!$R$2:$R$20000,0),"not found"))))))


Please help
 
Upvote 0
I can use this formula to find my first match but how can I change it, or use something else, to retrieve the second, third, and fourth matches?

HLOOKUP("CP ????",$M24:$BZ24,1,FALSE)
 
Upvote 0
Hello notruck,

I'll try to help but it is a bit hard to follow what you are trying to do. You posted data from columns E through L and say your data goes to column S, yet the formula you posted for the VLOOKUP has a range from M to BZ. I can't try your other formula to see what you are trying to return because I don't have the entire range. Can you try to post some sample data, what you are trying to do, and what you want returned? Or at least describe what you are trying to find in the sample above, and what it is you want for a result, and where you want the results returned.
 
Upvote 0
@notruck

What is the problem you want to solve? What do you want to see as concrete outcome regarding CP 0979?
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top