Multiple lookups using two criteria

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
168
(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:

CDE
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
 
Last edited:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,014
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jan 3, 2015
Messages
168
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

ABCDE
1ProductSKU
SYNARBALMr
SYNEOABUNDtr
SYNEOABUNDte
SYNARBALM50g
2Product CategoryCategory 1Category 2Category 2Category 1
3Product NameProduct 1Product 2Product 3Product 4
4Size500101050
5VariantMiniRetailTesterTester
6Selling Price$10$24.95$12$36
71
10g Aluminium - Jar & Lid SYN
81
91
101
112
122
132
142
153
163
173
184
194
204
215
225
235
246
Active Release Balm Retail - Label
256
Active Release Balm Bulk
266

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

Cheers, TT
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,014
Office Version
  1. 2016
Platform
  1. Windows
WTom0412,

I think this is what you asked for:

Worksheet 1 data

Wtom0412.xlsx
CDE
1ProductSKUTypeComponentName
2LABEL-SYNARBALMr299x148 Ink - Lotion, Shampoo, Conditioner, Body Wash
3LABEL-SYNARBALMr238mm Dia Blank Waterproof Label
4SYNARBALM50g150g Aluminium - Jar & Lid SYN
5SYNARBALM50g2Active Release Balm Retail - Label
6SYNARBALM50g6Active Release Balm Bulk
7SYNARBALMp1400g Standard Jar (Amber)
8SYNARBALMp2Active Release Balm Retail - Label
9SYNARBALMp6Active Release Balm Bulk
10SYNARBALMr110g Aluminium - Jar & Lid SYN
11SYNARBALMr6Active Release Balm Retail - Label
12SYNARBALMr6Active Release Balm Bulk
13SYNARBALMte2Tester Label
14SYNARBALMte6Active Release Balm *
15SYNBMLBALM6Active Release Balm Bulk
Worksheet1


Worksheet 2 with retrieval

Wtom0412.xlsx
ABCDE
1ProductSKUSYNARBALMrSYNEOABUNDtrSYNEOABUNDteSYNARBALM50g
2Product CategoryCategory 1Category 2Category 2Category 1
3Product NameProduct 1Product 2Product 3Product 4
4Size500101050
5VariantMiniRetailTesterTester
6Selling Price$10 $24.95 $12 $36
7110g Aluminium - Jar & Lid SYN  50g Aluminium - Jar & Lid SYN
81    
91    
102   Active Release Balm Retail - Label
112    
122    
132    
142    
156Active Release Balm Retail - Label  Active Release Balm Bulk
166Active Release Balm Bulk   
176    
186    
196    
206    
Worksheet2
Cell Formulas
RangeFormula
A7:A20A7=IFERROR(AGGREGATE(15,6,Worksheet1!$D$2:$D$999,ROW()-ROWS($A$1:$A$6)),"")
B7:E20B7=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

Board Regular
Joined
Jan 3, 2015
Messages
168

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2018
Messages
1,014
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jan 3, 2015
Messages
168
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,577
Messages
5,548,861
Members
410,881
Latest member
toonces
Top