Multiple lookups using two criteria

[wtom0412]

(sorry for the edit of this post, I clicked Post Thread before I finished instead of Preview!!)

Hello All,

I am trying to extract information from one workbook into another. I have two criteria that I need to match on, and there will be multiple items to extract.

Workbook one contains the data I need to extract, workbook two is the destination.

Workbook One:

	C	D	E
1	ProductSKU	Type	ComponentName
2	LABEL-SYNARBALMr	2	99x148 Ink - Lotion, Shampoo, Conditioner, Body Wash
3	LABEL-SYNARBALMr	2	38mm Dia Blank Waterproof Label
4	SYNARBALM50g	1	50g Aluminium - Jar & Lid SYN
5	SYNARBALM50g	2	Active Release Balm Retail - Label
6	SYNARBALM50g	6	Active Release Balm Bulk
7	SYNARBALMp	1	400g Standard Jar (Amber)
8	SYNARBALMp	2	Active Release Balm Retail - Label
9	SYNARBALMp	6	Active Release Balm Bulk
10	SYNARBALMr	1	10g Aluminium - Jar & Lid SYN
11	SYNARBALMr	6	Active Release Balm Retail - Label
12	SYNARBALMr	6	Active Release Balm Bulk
13	SYNARBALMte	2	Tester Label
14	SYNARBALMte	6	Active Release Balm *
15	SYNBMLBALM	6	Active Release Balm Bulk

My first search criteria is SYNARBALMr and the second Criteria is 6. The data that I need t return will be from E11 and E12.

Workbook 2:

This is where the data will end up. Data with the Type of 1 will go to Sheet1 Column A Row 1, Item Type 2 will go to Sheet1 Column C Row 1 and so on for all 6 Item types.

I have been struggling with this for hours, and I just don't know how to ask Google the correct question to help!!

Thank you all in advance.

Cheers, TT

 

[Toadstool]

Hi Wtom0412,

I don't quite understand "My first search criteria" but then followed by "This is where the data will end up. " which says it wants criteria in different columns?

Can you draft the other sheet showing how a search is specified and which data goes in the columns?

 

[wtom0412]

Hi Toadstool, thank you for your reply, I hope the following clears things up a bit and I don't confuse things more!!

My two Search Criteria are in Worksheet 2 Row 1 and Worksheet 2 Column A.

I have 312 Columns and 26 Rows in Worksheet 2 (311 Products), and 5 Columns and 1468 Rows in Worksheet 1.

I first need to search down Column C in Worksheet 1 using the ProductSKU from Worksheet 2 B1 "SYNARBALMr" as the search criteria, once found I need to see if there is a "1" in Worksheet 1 Column D and return the value from Column E - that value would be placed in Worksheet 2 B7. I would repeat the process again to see if there are any more "1" in Worksheet 1 Column C until none are found, then I would repeat and search in Worksheet 1 Column C still using "SYNARBALMr" but search for "2", then "3", then "4", then "5", then "6" until no more are found.

I then need to repeat the above using the value in Worksheet 2 C1 (SYNEOABUNDtr), all the way to Column LA2.

So with the two sheets as examples...

Worksheet 2 B7 would get the data from Worksheet 1 E10
Worksheet 2 B24 would get the data from Worksheet 1 E11
Worksheet 2 B25 would get the data from Worksheet 1 E12

	A	B	C	D	E
1	ProductSKU	SYNARBALMr	SYNEOABUNDtr	SYNEOABUNDte	SYNARBALM50g
2	Product Category	Category 1	Category 2	Category 2	Category 1
3	Product Name	Product 1	Product 2	Product 3	Product 4
4	Size	500	10	10	50
5	Variant	Mini	Retail	Tester	Tester
6	Selling Price	$10	$24.95	$12	$36
7	1	10g Aluminium - Jar & Lid SYN
8	1
9	1
10	1
11	2
12	2
13	2
14	2
15	3
16	3
17	3
18	4
19	4
20	4
21	5
22	5
23	5
24	6	Active Release Balm Retail - Label
25	6	Active Release Balm Bulk
26	6

I am sorry if this is a bit convoluted, but I am not sure how better to explain what I need?

Cheers, TT

 

[Toadstool]

WTom0412,

I think this is what you asked for:

Worksheet 1 data

Wtom0412.xlsx
	C	D	E
1	ProductSKU	Type	ComponentName
2	LABEL-SYNARBALMr	2	99x148 Ink - Lotion, Shampoo, Conditioner, Body Wash
3	LABEL-SYNARBALMr	2	38mm Dia Blank Waterproof Label
4	SYNARBALM50g	1	50g Aluminium - Jar & Lid SYN
5	SYNARBALM50g	2	Active Release Balm Retail - Label
6	SYNARBALM50g	6	Active Release Balm Bulk
7	SYNARBALMp	1	400g Standard Jar (Amber)
8	SYNARBALMp	2	Active Release Balm Retail - Label
9	SYNARBALMp	6	Active Release Balm Bulk
10	SYNARBALMr	1	10g Aluminium - Jar & Lid SYN
11	SYNARBALMr	6	Active Release Balm Retail - Label
12	SYNARBALMr	6	Active Release Balm Bulk
13	SYNARBALMte	2	Tester Label
14	SYNARBALMte	6	Active Release Balm *
15	SYNBMLBALM	6	Active Release Balm Bulk
Worksheet1

Worksheet 2 with retrieval

Wtom0412.xlsx
	A	B	C	D	E
1	ProductSKU	SYNARBALMr	SYNEOABUNDtr	SYNEOABUNDte	SYNARBALM50g
2	Product Category	Category 1	Category 2	Category 2	Category 1
3	Product Name	Product 1	Product 2	Product 3	Product 4
4	Size	500	10	10	50
5	Variant	Mini	Retail	Tester	Tester
6	Selling Price	$10	$24.95	$12	$36
7	1	10g Aluminium - Jar & Lid SYN			50g Aluminium - Jar & Lid SYN
8	1
9	1
10	2				Active Release Balm Retail - Label
11	2
12	2
13	2
14	2
15	6	Active Release Balm Retail - Label			Active Release Balm Bulk
16	6	Active Release Balm Bulk
17	6
18	6
19	6
20	6
Worksheet2

Cell Formulas
Range	Formula
A7:A20	A7	=IFERROR(AGGREGATE(15,6,Worksheet1!$D$2:$D$999,ROW()-ROWS($A$1:$A$6)),"")
B7:E20	B7	=IFERROR(INDEX(Worksheet1!$E$2:$E$999,AGGREGATE(15,6,ROW(Worksheet1!$E$2:$E$999)-ROW($A$1)/((Worksheet1!$C$2:$C$999=B$1)*(Worksheet1!$D$2:$D$999=$A7)),COUNTIF($A$6:$A7,$A7))),"")

 

[wtom0412]

Hi Toadstool,

Yes, according to your images, that is exactly what I need, thank you, but please forgive me, but I don't understand how this works, or which sheet I should be putting this in (I assume Sheet2?).

I am sorry to be so dumb!!

Cheers, TT

 

[Toadstool]

My post shows which is which. There are two sheets (tabs at the bottom of your screen).

Worksheet1: Contains your data, just columns C, D and E.

Worksheet2: Contains the data you supplied in your post for cells A1 to E6. The rows below row 6 are where I put the formulae:

A7 to A20 are shown but all these formulae should be copied down as far as you'll need to handle your data. The formula
=IFERROR(AGGREGATE(15,6,Worksheet1!$D$2:$D$999,ROW()-ROWS($A$1:$A$6)),"")
takes the Type from Worksheet1and puts them in ascending sequence (i.e. 3x"1", 5x"2" and 6x"6"). It's OK if you copy this formula down too far because it will just return an empty cell.

B7 should be copied down rows and across columns as far as needed because again it will just return an empty cell if not needed.
=IFERROR(INDEX(Worksheet1!$E$2:$E$999,AGGREGATE(15,6,ROW(Worksheet1!$E$2:$E$999)-ROW($A$1)/((Worksheet1!$C$2:$C$999=B$1)*(Worksheet1!$D$2:$D$999=$A7)),COUNTIF($A$6:$A7,$A7))),"")
Uses the INDEX to retrieve column E from Worksheet1 (ComponentName) where the Worksheet1 Product SKU in column A matches the Worksheet2 row 1 entry for this column, and the Worksheet1 Type in column D matches the Worksheet2 type we constructed in column A. The COUNTIF tells the AGGREGATE which occurrence to retrieve when there are more than one entries for that Type and Product SKU (e.g. for our Worksheet2 column B product SYNARBALMr we have two entries in Worksheet1 with a type of "6". In Worksheet2 B15 the COUNTIF($A$6:$A15,$A15) returns a 1 so the AGGREGATE gets the first match which is in row 11 of Worksheet1, "Active Release Balm Retail - Label". The Worksheet2 B16 COUNTIF($A$6:$A16,$A16) will return a 2 so we get the second entry from Worksheet1 for Type 6, product SYNARBALMr which is row 12, "Active Release Balm Bulk".

I hope that explains but what I posted are not images. If you create a new Workbook and two sheets named Worksheet1 and Worksheet2 then you can click the little pages on the top left of each of my posts and it copies the contents, including formulae, onto the clipboard. Then you paste them into your Worksheet1 then Worksheet2.

 

[wtom0412]

Oh my god Toadstool, this is amazing - I am in awe!!

Thank you so much for this, I wish I was a thousandth as clever. Works perfectly (know that I understand it) .

Cheers, TT