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 | | | | |
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 | | | | |
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