Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
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: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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
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 "Dan" wrote: 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: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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
Hi!
Try this: =LOOKUP(REPT("Z",255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0))) Biff "Dan" wrote in message ... 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: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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
Very nice, Biff
*********** Regards, Ron XL2002, WinXP-Pro "Biff" wrote: Hi! Try this: =LOOKUP(REPT("Z",255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0))) Biff "Dan" wrote in message ... 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: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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
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 "Biff" wrote: Hi! Try this: =LOOKUP(REPT("Z",255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0))) Biff "Dan" wrote in message ... 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: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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
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" wrote: 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 "Biff" wrote: Hi! Try this: =LOOKUP(REPT("Z",255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0))) Biff "Dan" wrote in message ... 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: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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
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 "Ron Coderre" wrote: 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 "Dan" wrote: 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: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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
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" wrote: 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" wrote: 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 "Biff" wrote: Hi! Try this: =LOOKUP(REPT("Z",255),A1:INDEX(A1:A65535,MATCH(K50 ,B1:B65535,0))) Biff "Dan" wrote in message ... 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: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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
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 "Dan" wrote: 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 "Ron Coderre" wrote: 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 "Dan" wrote: 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: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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need reference in adjacent column returned
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 "Ron Coderre" wrote: 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 "Dan" wrote: 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 "Ron Coderre" wrote: 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 "Dan" wrote: 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: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Maintain Relative Reference After Inserting a Column | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions | |||
I need to find the Average from Column A - but Reference Column B | Excel Worksheet Functions |