Return value or cell reference of Pattern Match from a Range

[notruck]

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
	E	F	G	H	I	J	K	L
1	CP 1817 TK 002	CP 1813 TK 001	CP 1813 TK 003	CP 1816 TK 011	CP 1817 TK 098
2	CP 5565 TK 029	CP 5565 TK 027	CP 5565 TK 008	CP 5565 TK 023	CP 5565 TK 002	CP 5565 TK 021	CP 5565	CP 5565 TK 004
3	CP 5074 TK 029	CP 5074 TK 027	CP 5074 TK 004	CP 5074 TK 002	CP 5073 TK 023	CP 5073 TK 021	CP 5074
4
5	CP 6104 TK 007	CP 6104 TK 005	CP 6103 TK 023	CP 6103 TK 021	CP 6103 TK 006	CP 6103 TK 004	CP 6103 TK 003	CP 6103 TK 002
6	CP 6917 TK 1
7	CP 5598 TK 002	CP 5598	CP 5597 TK 041	CP 5597 TK 043
8	CP 0981	CP 0981 TK 002	CP 0981 TK 005	CP 0981 TK 007	CP 0980 TK 001
9
10	CP 3984 TK 03
11	CP 0979	CP 0979 TK 005	CP 0979 TK 007	CP 0979 TK 002	CP 0979 TK 009	CP 0979 TK 001
12	CP 2452	CP 2452 TK 005	CP 2452 TK 007	CP 2452 TK 002	CP 2451 TK 003
13	CP 0978	CP 0978 TK 003	CP 0978 TK 023	CP 0978 TK 004	CP 0979 TK 005	CP 0979 TK 007
14	CP 5563 TK 009	CP 5563 TK 007	CP 5562 TK 008	CP 5562 TK 006	CP 5562 TK 005	CP 5562 TK 004	CP 5562 TK 003	CP 5562 TK 002
15	CP 5087 TK 031	CP 5086 TK 033	CP 5086 TK 029	CP 5086 TK 028	CP 5086 TK 027	CP 5086 TK 026	CP 5086 TK 024	CP 5086 TK 023
16	CP 0977	CP 0975 TK 015	CP 0975 TK 017	CP 0977 TK 002	CP 0977 TK 004	CP 0977 TK 006	CP 0977 TK 013	CP 0977 TK 003
17	CP 5087 TK 031	CP 5087 TK 003	CP 5087 TK 002	CP 5086 TK 029	CP 5086 TK 027	CP 5087
18	CP 5564 TK 003	CP 5564 TK 001	CP 5563 TK 009	CP 5563 TK 007	CP 5563 TK 004	CP 5563 TK 002	CP 5562 TK 005	CP 5563
19	CP 5088 TK 007	CP 5088 TK 005	CP 5088 TK 004	CP 5088 TK 002	CP 5087 TK 003	CP 5086 TK 027	CP 5088
20
21	CP 2431 TK 001
22	CP 1064 TK 007
23	CP 1062	CP 1063	CP 1063 TK 005	CP 1063 TK 003	CP 1063 TK 002	CP 1064 TK 007*
24	CP 6719 TK 53	CP 6719 TK 51	CP 6719 TK 29	CP 6720 TK 001	CP 6720 TK 005	CP 6720 TK 31	CP 6720 TK 33
25	CP 5715 TK 003
26	CP 7307 TK 13
27	CP 2438 TK 009	CP 2437 TK 001
28	CP 8086 TK 31
29	CP 8086 TK 31
30	CP 9325 TK 59
31	CP 0744	CP 0745	CP 0744 TK 013	CP 0744 TK 009	CP 0745 TK 017	CP 0745 TK 002	CP 0744 TK 002	CP 0744 TK 021
32	CP 5504 TK 009
33	CP 2116 TK 003
34	CP 6900 TK 005	CP 6901 TK 011
35
36	CP 7160 TK 1
37	CP 3105 TK 002	CP 3105 TK 001	CP 3104 TK 011	CP 3104 TK 009	CP 3105
38	CP 7377 TK 3
Sheet1

Cell Formulas
Range	Formula
E1	CP 1817 TK 002
E2	CP 5565 TK 029
E3	CP 5074 TK 029
E5	CP 6104 TK 007
E6	CP 6917 TK 1
E7	CP 5598 TK 002
E8	CP 0981
E10	CP 3984 TK 03
E11	CP 0979
E12	CP 2452
E13	CP 0978
E14	CP 5563 TK 009
E15	CP 5087 TK 031
E16	CP 0977
E17	CP 5087 TK 031
E18	CP 5564 TK 003
E19	CP 5088 TK 007
E21	CP 2431 TK 001
E22	CP 1064 TK 007
E23	CP 1062
E24	CP 6719 TK 53
E25	CP 5715 TK 003
E26	CP 7307 TK 13
F1	CP 1813 TK 001
F2	CP 5565 TK 027
F3	CP 5074 TK 027
F5	CP 6104 TK 005
F7	CP 5598
F8	CP 0981 TK 002
F11	CP 0979 TK 005
F12	CP 2452 TK 005
F13	CP 0978 TK 003
F14	CP 5563 TK 007
F15	CP 5086 TK 033
F16	CP 0975 TK 015
F17	CP 5087 TK 003
F18	CP 5564 TK 001
F19	CP 5088 TK 005
F23	CP 1063
F24	CP 6719 TK 51
G1	CP 1813 TK 003
G2	CP 5565 TK 008
G3	CP 5074 TK 004
G5	CP 6103 TK 023
G7	CP 5597 TK 041
G8	CP 0981 TK 005
G11	CP 0979 TK 007
G12	CP 2452 TK 007
G13	CP 0978 TK 023
G14	CP 5562 TK 008
G15	CP 5086 TK 029
G16	CP 0975 TK 017
G17	CP 5087 TK 002
G18	CP 5563 TK 009
G19	CP 5088 TK 004
G23	CP 1063 TK 005
G24	CP 6719 TK 29
H1	CP 1816 TK 011
H2	CP 5565 TK 023
H3	CP 5074 TK 002
H5	CP 6103 TK 021
H7	CP 5597 TK 043
H8	CP 0981 TK 007
H11	CP 0979 TK 002
H12	CP 2452 TK 002
H13	CP 0978 TK 004
H14	CP 5562 TK 006
H15	CP 5086 TK 028
H16	CP 0977 TK 002
H17	CP 5086 TK 029
H18	CP 5563 TK 007
H19	CP 5088 TK 002
H23	CP 1063 TK 003
H24	CP 6720 TK 001
I1	CP 1817 TK 098
I2	CP 5565 TK 002
I3	CP 5073 TK 023
I5	CP 6103 TK 006
I8	CP 0980 TK 001
I11	CP 0979 TK 009
I12	CP 2451 TK 003
I13	CP 0979 TK 005
I14	CP 5562 TK 005
I15	CP 5086 TK 027
I16	CP 0977 TK 004
I17	CP 5086 TK 027
I18	CP 5563 TK 004
I19	CP 5087 TK 003
I23	CP 1063 TK 002
I24	CP 6720 TK 005
J2	CP 5565 TK 021
J3	CP 5073 TK 021
J5	CP 6103 TK 004
J11	CP 0979 TK 001
J13	CP 0979 TK 007
J14	CP 5562 TK 004
J15	CP 5086 TK 026
J16	CP 0977 TK 006
J17	CP 5087
J18	CP 5563 TK 002
J19	CP 5086 TK 027
J23	CP 1064 TK 007*
J24	CP 6720 TK 31

 

[notruck]

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

 

[notruck]

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)

 

[Joyner]

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.

 

[Aladin Akyurek]

@notruck

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