software

I have two columns with multiple sets of data, based on the entered value I
need the corresponding header returned.

For example:
Column A Column B
Apples Green
Red
Granny Smith
Mutsu
Red Delicious

Oranges Naval
Manderin
Clementine
Tangerine

Now based on a response in another sheet I need to return either quot;Applequot; or
quot;Orangequot;.

=INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I copy
Apple next to each apple type, but for other items the list of options can
grow dramatically.

Thanks

Dan

Try something like this:

Using your sample data with Category in Col_A and Items in Col_B

C1: Granny Smith
D1:
=INDEX(A1:A10,MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))

Note 1: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

In that example, D1 equates to quot;Applesquot;.

Note 2: In case the posting wraps incorrectly, there are NO spaces in that
formula.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Danquot; wrote:

gt; I have two columns with multiple sets of data, based on the entered value I
gt; need the corresponding header returned.
gt;
gt; For example:
gt; Column A Column B
gt; Apples Green
gt; Red
gt; Granny Smith
gt; Mutsu
gt; Red Delicious
gt;
gt; Oranges Naval
gt; Manderin
gt; Clementine
gt; Tangerine
gt;
gt; Now based on a response in another sheet I need to return either quot;Applequot; or
gt; quot;Orangequot;.
gt;
gt; =INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I copy
gt; Apple next to each apple type, but for other items the list of options can
gt; grow dramatically.
gt;
gt; Thanks
gt;
gt; Dan

Hi!

Try this:

=LOOKUP(REPT(quot;Zquot;,255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0)))

Biff

quot;Danquot; gt; wrote in message
...
gt;I have two columns with multiple sets of data, based on the entered value I
gt; need the corresponding header returned.
gt;
gt; For example:
gt; Column A Column B
gt; Apples Green
gt; Red
gt; Granny Smith
gt; Mutsu
gt; Red Delicious
gt;
gt; Oranges Naval
gt; Manderin
gt; Clementine
gt; Tangerine
gt;
gt; Now based on a response in another sheet I need to return either quot;Applequot;
gt; or
gt; quot;Orangequot;.
gt;
gt; =INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I
gt; copy
gt; Apple next to each apple type, but for other items the list of options can
gt; grow dramatically.
gt;
gt; Thanks
gt;
gt; Dan
Very nice, Biff***********
Regards,
Ron

XL2002, WinXP-Proquot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =LOOKUP(REPT(quot;Zquot;,255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0)))
gt;
gt; Biff
gt;
gt; quot;Danquot; gt; wrote in message
gt; ...
gt; gt;I have two columns with multiple sets of data, based on the entered value I
gt; gt; need the corresponding header returned.
gt; gt;
gt; gt; For example:
gt; gt; Column A Column B
gt; gt; Apples Green
gt; gt; Red
gt; gt; Granny Smith
gt; gt; Mutsu
gt; gt; Red Delicious
gt; gt;
gt; gt; Oranges Naval
gt; gt; Manderin
gt; gt; Clementine
gt; gt; Tangerine
gt; gt;
gt; gt; Now based on a response in another sheet I need to return either quot;Applequot;
gt; gt; or
gt; gt; quot;Orangequot;.
gt; gt;
gt; gt; =INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I
gt; gt; copy
gt; gt; Apple next to each apple type, but for other items the list of options can
gt; gt; grow dramatically.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Dan
gt;
gt;
gt;

Hello Gentlemen,

Both versions work. I understand how the following eqution works:
=INDEX(A1:A10,MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))

I am wondering how this equation works:

=LOOKUP(REPT(quot;Zquot;,255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0)))

How does the REPT factor in to the equation to allow the response to be
returned. I understand the equation up until this point.

Thanks for all the help.

Danquot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =LOOKUP(REPT(quot;Zquot;,255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0)))
gt;
gt; Biff
gt;
gt; quot;Danquot; gt; wrote in message
gt; ...
gt; gt;I have two columns with multiple sets of data, based on the entered value I
gt; gt; need the corresponding header returned.
gt; gt;
gt; gt; For example:
gt; gt; Column A Column B
gt; gt; Apples Green
gt; gt; Red
gt; gt; Granny Smith
gt; gt; Mutsu
gt; gt; Red Delicious
gt; gt;
gt; gt; Oranges Naval
gt; gt; Manderin
gt; gt; Clementine
gt; gt; Tangerine
gt; gt;
gt; gt; Now based on a response in another sheet I need to return either quot;Applequot;
gt; gt; or
gt; gt; quot;Orangequot;.
gt; gt;
gt; gt; =INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I
gt; gt; copy
gt; gt; Apple next to each apple type, but for other items the list of options can
gt; gt; grow dramatically.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Dan
gt;
gt;
gt;

Per Excel Help:
quot;If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value. quot;

In the case of Biff's excellent formula, the LOOKUP function won't be able
to find a match for 255 Z's. Consequently, it will match the last non-blank
value in the lookup range....which will be the category for the selected
fruit.

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP-Proquot;Danquot; wrote:

gt; Hello Gentlemen,
gt;
gt; Both versions work. I understand how the following eqution works:
gt; =INDEX(A1:A10,MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))
gt;
gt; I am wondering how this equation works:
gt;
gt; =LOOKUP(REPT(quot;Zquot;,255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0)))
gt;
gt; How does the REPT factor in to the equation to allow the response to be
gt; returned. I understand the equation up until this point.
gt;
gt; Thanks for all the help.
gt;
gt; Dan
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt; gt; Hi!
gt; gt;
gt; gt; Try this:
gt; gt;
gt; gt; =LOOKUP(REPT(quot;Zquot;,255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0)))
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;Danquot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I have two columns with multiple sets of data, based on the entered value I
gt; gt; gt; need the corresponding header returned.
gt; gt; gt;
gt; gt; gt; For example:
gt; gt; gt; Column A Column B
gt; gt; gt; Apples Green
gt; gt; gt; Red
gt; gt; gt; Granny Smith
gt; gt; gt; Mutsu
gt; gt; gt; Red Delicious
gt; gt; gt;
gt; gt; gt; Oranges Naval
gt; gt; gt; Manderin
gt; gt; gt; Clementine
gt; gt; gt; Tangerine
gt; gt; gt;
gt; gt; gt; Now based on a response in another sheet I need to return either quot;Applequot;
gt; gt; gt; or
gt; gt; gt; quot;Orangequot;.
gt; gt; gt;
gt; gt; gt; =INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I
gt; gt; gt; copy
gt; gt; gt; Apple next to each apple type, but for other items the list of options can
gt; gt; gt; grow dramatically.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; Dan
gt; gt;
gt; gt;
gt; gt;

Ron,

Quick question. Within the formula pleae explain the
MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A15,MATCH(C1,B1:B15,0),1))))

Specificaly once the 1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))
is evaluated.

Now I have
MATCH(2,{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!}))

Note: I extended the range and added a thrid choice to better understand the
equation.

How does MATCH return the second quot;1quot; response versus the first or third quot;1quot;,
which is the correct response.

Thanks

Dan

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; Using your sample data with Category in Col_A and Items in Col_B
gt;
gt; C1: Granny Smith
gt; D1:
gt; =INDEX(A1:A10,MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))
gt;
gt; Note 1: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; press [Enter].
gt;
gt; In that example, D1 equates to quot;Applesquot;.
gt;
gt; Note 2: In case the posting wraps incorrectly, there are NO spaces in that
gt; formula.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Danquot; wrote:
gt;
gt; gt; I have two columns with multiple sets of data, based on the entered value I
gt; gt; need the corresponding header returned.
gt; gt;
gt; gt; For example:
gt; gt; Column A Column B
gt; gt; Apples Green
gt; gt; Red
gt; gt; Granny Smith
gt; gt; Mutsu
gt; gt; Red Delicious
gt; gt;
gt; gt; Oranges Naval
gt; gt; Manderin
gt; gt; Clementine
gt; gt; Tangerine
gt; gt;
gt; gt; Now based on a response in another sheet I need to return either quot;Applequot; or
gt; gt; quot;Orangequot;.
gt; gt;
gt; gt; =INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I copy
gt; gt; Apple next to each apple type, but for other items the list of options can
gt; gt; grow dramatically.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Dan

Thanks. After playing with the equation, and also noticed the explanation
for LOOKUP I realized it would return the last value in the array. The array
will be limited by the value I am looking up therefore the answer is always
the last in the array.

This explanation will help others use these notes.

Thanks agian to both of you.

Dan

quot;Ron Coderrequot; wrote:

gt; Per Excel Help:
gt; quot;If LOOKUP can't find the lookup_value, it matches the largest value in
gt; lookup_vector that is less than or equal to lookup_value. quot;
gt;
gt; In the case of Biff's excellent formula, the LOOKUP function won't be able
gt; to find a match for 255 Z's. Consequently, it will match the last non-blank
gt; value in the lookup range....which will be the category for the selected
gt; fruit.
gt;
gt; I hope that helps.
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Danquot; wrote:
gt;
gt; gt; Hello Gentlemen,
gt; gt;
gt; gt; Both versions work. I understand how the following eqution works:
gt; gt; =INDEX(A1:A10,MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))
gt; gt;
gt; gt; I am wondering how this equation works:
gt; gt;
gt; gt; =LOOKUP(REPT(quot;Zquot;,255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0)))
gt; gt;
gt; gt; How does the REPT factor in to the equation to allow the response to be
gt; gt; returned. I understand the equation up until this point.
gt; gt;
gt; gt; Thanks for all the help.
gt; gt;
gt; gt; Dan
gt; gt;
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt; gt; Hi!
gt; gt; gt;
gt; gt; gt; Try this:
gt; gt; gt;
gt; gt; gt; =LOOKUP(REPT(quot;Zquot;,255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0)))
gt; gt; gt;
gt; gt; gt; Biff
gt; gt; gt;
gt; gt; gt; quot;Danquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt;I have two columns with multiple sets of data, based on the entered value I
gt; gt; gt; gt; need the corresponding header returned.
gt; gt; gt; gt;
gt; gt; gt; gt; For example:
gt; gt; gt; gt; Column A Column B
gt; gt; gt; gt; Apples Green
gt; gt; gt; gt; Red
gt; gt; gt; gt; Granny Smith
gt; gt; gt; gt; Mutsu
gt; gt; gt; gt; Red Delicious
gt; gt; gt; gt;
gt; gt; gt; gt; Oranges Naval
gt; gt; gt; gt; Manderin
gt; gt; gt; gt; Clementine
gt; gt; gt; gt; Tangerine
gt; gt; gt; gt;
gt; gt; gt; gt; Now based on a response in another sheet I need to return either quot;Applequot;
gt; gt; gt; gt; or
gt; gt; gt; gt; quot;Orangequot;.
gt; gt; gt; gt;
gt; gt; gt; gt; =INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I
gt; gt; gt; gt; copy
gt; gt; gt; gt; Apple next to each apple type, but for other items the list of options can
gt; gt; gt; gt; grow dramatically.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt; gt; Dan
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;

Dan:

In this part of the formula:
A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)

The MATCH section above restricts the referenced range to end in the cell to
the left of the matched item. If the matched item is quot;Granny Smithquot; and that
value is in cell B8, the formula will evaulate to A1:A8. In the example you
posted, you wouldn't see any #DIV/0!'s or 1's beyond the 8th item.
Consequently, the last 1 would pertain to the last category before, or at,
the quot;Granny Smithquot; value.

Next: The selection of the correct 1 in the array.
When the list of values is numeric and the 3rd argument of the MATCH
function is either omitted or set to 1, indicating an approximate match, the
MATCH function assumes that the values are in ascending order. If the values
are NOT in ascending order, the function will stop at the first value that is
larger than the searched value and returns the previous numeric value that is
less than the searched value. #DIV/0!'s are ignored. I'm pretty sure it
would even skip an exact match further down the list. (The best way to
understand the function is probably to experiment with it.) If no values are
larger than the searched value, it will match the last value that is less
than the searched value. In our case, the values are either #DIV/0!'s or 1's
and we are searching for a 2, so there are no ambiguities.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Danquot; wrote:

gt; Ron,
gt;
gt; Quick question. Within the formula pleae explain the
gt; MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A15,MATCH(C1,B1:B15,0),1))))
gt;
gt; Specificaly once the 1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))
gt; is evaluated.
gt;
gt; Now I have
gt; MATCH(2,{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!}))
gt;
gt; Note: I extended the range and added a thrid choice to better understand the
gt; equation.
gt;
gt; How does MATCH return the second quot;1quot; response versus the first or third quot;1quot;,
gt; which is the correct response.
gt;
gt; Thanks
gt;
gt; Dan
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; Using your sample data with Category in Col_A and Items in Col_B
gt; gt;
gt; gt; C1: Granny Smith
gt; gt; D1:
gt; gt; =INDEX(A1:A10,MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))
gt; gt;
gt; gt; Note 1: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; gt; press [Enter].
gt; gt;
gt; gt; In that example, D1 equates to quot;Applesquot;.
gt; gt;
gt; gt; Note 2: In case the posting wraps incorrectly, there are NO spaces in that
gt; gt; formula.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Danquot; wrote:
gt; gt;
gt; gt; gt; I have two columns with multiple sets of data, based on the entered value I
gt; gt; gt; need the corresponding header returned.
gt; gt; gt;
gt; gt; gt; For example:
gt; gt; gt; Column A Column B
gt; gt; gt; Apples Green
gt; gt; gt; Red
gt; gt; gt; Granny Smith
gt; gt; gt; Mutsu
gt; gt; gt; Red Delicious
gt; gt; gt;
gt; gt; gt; Oranges Naval
gt; gt; gt; Manderin
gt; gt; gt; Clementine
gt; gt; gt; Tangerine
gt; gt; gt;
gt; gt; gt; Now based on a response in another sheet I need to return either quot;Applequot; or
gt; gt; gt; quot;Orangequot;.
gt; gt; gt;
gt; gt; gt; =INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I copy
gt; gt; gt; Apple next to each apple type, but for other items the list of options can
gt; gt; gt; grow dramatically.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; Dan

Ron,

Thanks for the details. What I was trying to understand was the purpose of
the constant quot;2quot; within you equation. Your second statement explained it.
Now I understand how it related to Biff's use of REPT(quot;Zquot;,255) statement.

This understanding will aloow me to develop additional formulas int he future.

Once again, I appreciate the assistance.

Regards,

Dan

quot;Ron Coderrequot; wrote:

gt; Dan:
gt;
gt; In this part of the formula:
gt; A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)
gt;
gt; The MATCH section above restricts the referenced range to end in the cell to
gt; the left of the matched item. If the matched item is quot;Granny Smithquot; and that
gt; value is in cell B8, the formula will evaulate to A1:A8. In the example you
gt; posted, you wouldn't see any #DIV/0!'s or 1's beyond the 8th item.
gt; Consequently, the last 1 would pertain to the last category before, or at,
gt; the quot;Granny Smithquot; value.
gt;
gt; Next: The selection of the correct 1 in the array.
gt; When the list of values is numeric and the 3rd argument of the MATCH
gt; function is either omitted or set to 1, indicating an approximate match, the
gt; MATCH function assumes that the values are in ascending order. If the values
gt; are NOT in ascending order, the function will stop at the first value that is
gt; larger than the searched value and returns the previous numeric value that is
gt; less than the searched value. #DIV/0!'s are ignored. I'm pretty sure it
gt; would even skip an exact match further down the list. (The best way to
gt; understand the function is probably to experiment with it.) If no values are
gt; larger than the searched value, it will match the last value that is less
gt; than the searched value. In our case, the values are either #DIV/0!'s or 1's
gt; and we are searching for a 2, so there are no ambiguities.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Danquot; wrote:
gt;
gt; gt; Ron,
gt; gt;
gt; gt; Quick question. Within the formula pleae explain the
gt; gt; MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A15,MATCH(C1,B1:B15,0),1))))
gt; gt;
gt; gt; Specificaly once the 1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))
gt; gt; is evaluated.
gt; gt;
gt; gt; Now I have
gt; gt; MATCH(2,{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!}))
gt; gt;
gt; gt; Note: I extended the range and added a thrid choice to better understand the
gt; gt; equation.
gt; gt;
gt; gt; How does MATCH return the second quot;1quot; response versus the first or third quot;1quot;,
gt; gt; which is the correct response.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Dan
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try something like this:
gt; gt; gt;
gt; gt; gt; Using your sample data with Category in Col_A and Items in Col_B
gt; gt; gt;
gt; gt; gt; C1: Granny Smith
gt; gt; gt; D1:
gt; gt; gt; =INDEX(A1:A10,MATCH(2,1/(1-ISBLANK(A1:INDEX(A1:A10,MATCH(C1,B1:B10,0),1)))))
gt; gt; gt;
gt; gt; gt; Note 1: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; gt; gt; press [Enter].
gt; gt; gt;
gt; gt; gt; In that example, D1 equates to quot;Applesquot;.
gt; gt; gt;
gt; gt; gt; Note 2: In case the posting wraps incorrectly, there are NO spaces in that
gt; gt; gt; formula.
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Danquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have two columns with multiple sets of data, based on the entered value I
gt; gt; gt; gt; need the corresponding header returned.
gt; gt; gt; gt;
gt; gt; gt; gt; For example:
gt; gt; gt; gt; Column A Column B
gt; gt; gt; gt; Apples Green
gt; gt; gt; gt; Red
gt; gt; gt; gt; Granny Smith
gt; gt; gt; gt; Mutsu
gt; gt; gt; gt; Red Delicious
gt; gt; gt; gt;
gt; gt; gt; gt; Oranges Naval
gt; gt; gt; gt; Manderin
gt; gt; gt; gt; Clementine
gt; gt; gt; gt; Tangerine
gt; gt; gt; gt;
gt; gt; gt; gt; Now based on a response in another sheet I need to return either quot;Applequot; or
gt; gt; gt; gt; quot;Orangequot;.
gt; gt; gt; gt;
gt; gt; gt; gt; =INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B6553 5,0)) will work if I copy
gt; gt; gt; gt; Apple next to each apple type, but for other items the list of options can
gt; gt; gt; gt; grow dramatically.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt; gt; Dan