Hi, Rich-
First, let me say "Nice job" in detailing the column layout in your
original post. These often get mangled when they get posted, but yours
survived intact and made determining the answer very easy.

This is a fairly involved process, because you need to create a unique
identifier for each line on the sprdsht. However, you can't use the
entry on each line because "Red" can apply to both apples and pears.
The solution is to create a unique entry for each line by concatenating
"Red" with "Apple" and "Apple Tree". This will distinguish apple tree
"Red" from pear tree "Red".

Here's how I did it:
1. Make a copy of the original sprdsht so you don't lose irrecoverable
data. Work out all these details on the copied data until you are 100%
confident you won't lose data.
2. At the top of both List1 and List2, enter a single blank line.
(This is to accommodate a formula that returns a blank entry under
certain circumstances.)
3. I assumed List1 and List2 appear in separate tabs called List1 and
List2, and that your original data appears in columns A - D. I assumed
your original data appeared in Row 1 which is now Row 2 because of
instruction 2 above. Leave column E blank; in cell F2 enter this
formula:
=IF($D20,A2&F1,"")
This applies to both List1 and List2.
4. Copy this formula across to column G and down for as many rows as
necessary.
5. In cell H2 enter this formula, and copy down for all rows:
=IF(AND(C2<"",D20),C2,"")
6. In cell I2 enter this formula:
=IF(D20,F2&G2&H2,"")
This creates the unique identifier for each row. Copy it down for all
rows.
7. In both List1 and List2, cell J2, enter this formula:
=IF(A2<"",A2,"")
Copy this formula over to cell M and down for all rows. This
reproduces existing data, but greases the skids for a lookup function
later.
8. Copy all entries in List1 column I, and paste them into a new tab
called "Summary" Column A. Copy all entries from List 2 Column I and
paste them into the Summary tab in column A *below* the list 1 entries.
9. Highlight Summary tab column A, and sort ascending.
10. This part can be tricky and unpleasant: remove any blank rows that
appear above your data, and remove any duplicate entries that appear in
Column A. The result is a list of unique identifiers from both tabs;
each unique identifier is represented only once. Remove any blank rows
at the top such that your data appears in cell Summary!A1.
11. In the Summary tab, insert two rows at the top of your data, such
that data now starts in cell Summary!A3. Enter the following values in
these cells:
B2: 2
C2: 3
D2: 4
E2: 5
F2: 5
Create headers in row 1, something along the lines of
B1: Plant
C1: Variety
D1: Color
E1: List1 price
F1: List2 price
12. In Summary!B3 enter this formula:
=IF(ISERROR(VLOOKUP($A3,List1!$I$2:$M$21,B$2,0)),V LOOKUP($A3,List2!$I$2:$M$21,B$2,0),VLOOKUP($A3,Lis t1!$I$2:$M$21,B$2,0))
This formula returns a value from List1 if there is a value to be
found, or looks in List2 if the value is not in List1. Copy this
formula across to cell D3 and down for all rows.
13. In Summary!E3 enter this formula:
=IF(ISERROR(VLOOKUP($A3,List1!$I$2:$M$21,E$2,0))," ",VLOOKUP($A3,List1!$I$2:$M$21,E$2,0))
This formula returns a value from List1 if it can be found, or else
leaves the entry blank.
14. In Summary!F3 enter this formula:
=IF(ISERROR(VLOOKUP($A3,List2!$I$2:$M$21,F$2,0))," ",VLOOKUP($A3,List2!$I$2:$M$21,F$2,0))
This formula returns a value from List2 if it can be found, or else
leaves the entry blank.

NOTE! Read the Help! entry for the VLOOKUP function if you're not
already familiar with it, and adjust the VLOOKUP formulas to match the
arrays in List1 and List2.

And that's it, altho you may want to make some cosmetic changes such as
hiding Summary!Row 2 and Summary!Column A, and inserting separator rows
between plant types.

Contact me, , with a valid email address and I'll
send you my workup Excel file.

Dave O