Need reference in adjacent column returned

[Dan]

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 "Apple" or
"Orange".

=INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B65535,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

 

[Ron Coderre]

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 "Apples".

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

Does that help?

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

XL2002, WinXP-Pro

 

[Biff]

Hi!

Try this:

=LOOKUP(REPT("Z",255),A1:INDEX(A1:A65535,MATCH(K50,B1:B65535,0)))

Biff

 

[Ron Coderre]

Very nice, Biff


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

XL2002, WinXP-Pro

 

[Dan]

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("Z",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.

Dan

 

[Ron Coderre]

Per Excel Help:
"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. "

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

 

[Dan]

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 "1" response versus the first or third "1",
which is the correct response.

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 "Apples".

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

Does that help?

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

XL2002, WinXP-Pro

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 "Apple" or
"Orange".

=INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B65535,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

 

[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

 

[Ron Coderre]

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 "Granny Smith" 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 "Granny Smith" 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-Pro

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 "1" response versus the first or third "1",
which is the correct response.

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 "Apples".

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

Does that help?

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

XL2002, WinXP-Pro

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 "Apple" or
"Orange".

=INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B65535,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

 

[Dan]

Ron,

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

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

Once again, I appreciate the assistance.

Regards,

Dan

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 "Granny Smith" 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 "Granny Smith" 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-Pro

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 "1" response versus the first or third "1",
which is the correct response.

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 "Apples".

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

Does that help?

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

XL2002, WinXP-Pro


:

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 "Apple" or
"Orange".

=INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B65535,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