Vlookup - create new list from existing list based on partial match

[unknown_brother]

I'm trying to use formulae only to create a new list of data from a master data set using criteria in a lookup column.
I'm hoping for my formula to follow these steps:
1 Take value in lookup list (cell will be locked i imagine).
2 Lookup cells in B1:B15 partially containing value in $D$2.
3 When a match is found, stop, copy complete value in column B plus adjacent value in column C. Add value to G & H column (G4:H4 for first entry in example)
4 After first entry, formula will move to next row in column G (G4) (MOst likely drag cells G4:H4)
5 Move to next row in column B and continue lookup.
6 Repeat step 3 & 4 everytime a match is found.

This is pretty much the jist of it. I'm new to formulae in excel so any help would be appreciated.
vlookup is great but couldn't combine with other function to do step 4

[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AC10-F300042 [/TD]
[TD]3[/TD]
[TD]Lookup criteria[/TD]
[TD][/TD]
[TD][/TD]
[TD]New list[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AC10-F300042 [/TD]
[TD]5[/TD]
[TD]-LL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GW10-F300024[/TD]
[TD]3[/TD]
[TD]-LQ[/TD]
[TD][/TD]
[TD][/TD]
[TD]-LL List[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AC10-F30001 [/TD]
[TD]8[/TD]
[TD]-F3[/TD]
[TD][/TD]
[TD][/TD]
[TD]AC13-LLU007[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]DR10-F3000521[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AC10-LLU007[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AC10-F3000324[/TD]
[TD]77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DR10-LLU003[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AC13-LLU007[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]AC10-LLU003 [/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]DR10-LLU003 [/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]AC10-LQL078[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]BC10-LQL002[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]AC10-LQL002[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]AC10-LQL002[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In G4 control+shift+enter, not just enter, and copy down"

=IFERROR(INDEX($B$1:$B$13,SMALL(IF(ISNUMBER(SEARCH($D$2,$B$1:$B$13)),ROW($B$1:$B$13)-ROW($B$1)+1),ROWS($G$4:G4))),"")

In H4 just enter and copy down:

=IF($G4="","",SUMIFS($C$1:$C$13,$B$1:$B$13,$G4))

Repeat the foregoing in a separate location For D3, etc.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is any use.
All standard-entry formulas:
G3 copied across as far as you might need after adjusting my range $D$2:$D$10 so that it will be enough to cover the longest list that you might use in that column.
G4 & H4 copied down as far as you might need.
G4:Hx copied and pasted to I4, K4 etc

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
1	AC10-F300042	3	Lookup criteria			New lists
2	AC10-F300042	5	-LL
3	GW10-F300024	3	-LQ			-LL	Value	-LQ	Value	-F3	Value
4	AC10-F30001	8	-F3			AC13-LLU007	6	AC10-LQL078	5	AC10-F300042	3
5	DR10-F3000521	8				AC10-LLU003	6	BC10-LQL002	5	AC10-F300042	5
6	AC10-F3000324	77				DR10-LLU003	5	AC10-LQL002	7	GW10-F300024	3
7	AC13-LLU007	6						AC10-LQL002	6	AC10-F30001	8
8	AC10-LLU003	6								DR10-F3000521	8
9	DR10-LLU003	5								AC10-F3000324	77
10	AC10-LQL078	5
11	BC10-LQL002	5
12	AC10-LQL002	7
13	AC10-LQL002	6
Lists

 

[unknown_brother]

THanks a mil.
It was very helpful.

How would I go about this if I wanted to create a single list with values containing only the following characters from my look up criteria '-LQ' and 'F3'?

 

[Aladin Akyurek]

THanks a mil.
It was very helpful.

How would I go about this if I wanted to create a single list with values containing only the following characters from my look up criteria '-LQ' and 'F3'?

Is this the output you expect from the sample you already provided?

[TABLE="class: grid, width: 232"]
<tbody>[TR]
[TD]New list[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AC10-F300042[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]GW10-F300024[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]AC10-F30001[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]DR10-F3000521[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]AC10-F3000324[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]AC10-LQL078[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BC10-LQL002[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]AC10-LQL002[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[unknown_brother]

Is this the output you expect from the sample you already provided?

[TABLE="class: grid, width: 232"]
<tbody>[TR]
[TD]New list[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AC10-F300042[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]GW10-F300024[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]AC10-F30001[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]DR10-F3000521[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]AC10-F3000324[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]AC10-LQL078[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BC10-LQL002[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]AC10-LQL002[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Correct. This would be a modification of my original request.
New list would only contain -F3 and -LQ values

 

[Aladin Akyurek]

Correct. This would be a modification of my original request.
New list would only contain -F3 and -LQ values

D2:D3 houses the criteria -F3 and -LQ.

In G2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$13,SMALL(IF(FREQUENCY(IF($B$1:$B$13<>"",IF(ISNUMBER(MATCH(LEFT(REPLACE($B$1:$B$13,1,FIND("-",$B$1:$B$13)-1,""),3),$D$2:$D$3,0)),MATCH($B$1:$B$13,$B$1:$B$13,0))),ROW($B$1:$B$13)-ROW($B$1)+1),ROW($B$1:$B$13)-ROW($B$1)+1),ROWS($G$2:G2))),"")

In H2 just enter and copy down:

IF($G2="","",SUMIFS($C$2:$C$13,$B$2:$B$13,$G2))

 

[Peter_SSs]

Correct.

I'm not convinced that you have checked the list in post #5 accurately. My reasons for doubts are ..

a) If 13 is the correct result for the last row, then surely the result for the first row should be 8, not 5.


b) Your original post contained detailed steps which included

3 When a match is found, stop, copy complete value in column B plus adjacent value in column C. Add value to G & H column (G4:H4 for first entry in example)
4 After first entry, formula will move to next row in column G (G4) (MOst likely drag cells G4:H4)
5 Move to next row in column B and continue lookup.
6 Repeat step 3 & 4 everytime a match is found.

If those steps were followed then one part of the results would be

AC10-LQL002 ...7
AC10-LQL002 ...6

not

AC10-LQL002 ...13


In case those original steps are in fact correct and you want each row individually (ie 7 & 6 not 13), then try

Excel Workbook
	B	C	D	E	F	G	H
1	AC10-F300042	3	Lookup criteria
2	AC10-F300042	5	-LQ
3	GW10-F300024	3	-F3
4	AC10-F30001	8				AC10-F300042	3
5	DR10-F3000521	8				AC10-F300042	5
6	AC10-F3000324	77				GW10-F300024	3
7	AC13-LLU007	6				AC10-F30001	8
8	AC10-LLU003	6				DR10-F3000521	8
9	DR10-LLU003	5				AC10-F3000324	77
10	AC10-LQL078	5				AC10-LQL078	5
11	BC10-LQL002	5				BC10-LQL002	5
12	AC10-LQL002	7				AC10-LQL002	7
13	AC10-LQL002	6				AC10-LQL002	6
14
Lists (2)

If you do want identical codes combined into one row (ie 13 instead of 7 & 6) then you could consider this alternative

Excel Workbook
	B	C	D	E	F	G	H
1	AC10-F300042	3	Lookup criteria
2	AC10-F300042	5	-LQ
3	GW10-F300024	3	-F3
4	AC10-F30001	8				AC10-F300042	8
5	DR10-F3000521	8				GW10-F300024	3
6	AC10-F3000324	77				AC10-F30001	8
7	AC13-LLU007	6				DR10-F3000521	8
8	AC10-LLU003	6				AC10-F3000324	77
9	DR10-LLU003	5				AC10-LQL078	5
10	AC10-LQL078	5				BC10-LQL002	5
11	BC10-LQL002	5				AC10-LQL002	13
12	AC10-LQL002	7
13	AC10-LQL002	6
14
Lists (3)