| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is INDEX,MATCH the best way?
here is the problem:
A B C 1 Apple .40 =Index(A1:A5,Match(Large(B1:B5,1),0)) (Returns-Peach) 2 Orange .30 =Index(A1:A5,Match(Large(B1:B5,2),0)) (Returns-Apple) 3 Banana .20 =Index(A1:A5,Match(large(B1:B5,3),0)) (Returns-Apple) 4 Pear .40 =Index(A1:A5,Match(Large(B1:B5,4),0)) (Returns-Orange) 5 Peach .50 =Index(A1:A5,Match(Large(B1:B5,5),0)) (Returns-Banana) Is there a way to have the formula in C3 return Pear? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is INDEX,MATCH the best way?
Try this array formula** :
Item = column A Cost = column B =INDEX(Item,MATCH(LARGE(Cost-ROW(Cost)/10^10,ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0)) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "M.A.Tyler" <Great Lakes State wrote in message ... here is the problem: A B C 1 Apple .40 =Index(A1:A5,Match(Large(B1:B5,1),0)) (Returns-Peach) 2 Orange .30 =Index(A1:A5,Match(Large(B1:B5,2),0)) (Returns-Apple) 3 Banana .20 =Index(A1:A5,Match(large(B1:B5,3),0)) (Returns-Apple) 4 Pear .40 =Index(A1:A5,Match(Large(B1:B5,4),0)) (Returns-Orange) 5 Peach .50 =Index(A1:A5,Match(Large(B1:B5,5),0)) (Returns-Banana) Is there a way to have the formula in C3 return Pear? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is INDEX,MATCH the best way?
OK, now I'm getting a VALUE error. Could it be possible that if some of the
cells in column A (Items) are blank or "" that the division involved would cause this error? More importantly is there a fix? "T. Valko" wrote: Try this array formula** : Item = column A Cost = column B =INDEX(Item,MATCH(LARGE(Cost-ROW(Cost)/10^10,ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0)) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "M.A.Tyler" <Great Lakes State wrote in message ... here is the problem: A B C 1 Apple .40 =Index(A1:A5,Match(Large(B1:B5,1),0)) (Returns-Peach) 2 Orange .30 =Index(A1:A5,Match(Large(B1:B5,2),0)) (Returns-Apple) 3 Banana .20 =Index(A1:A5,Match(large(B1:B5,3),0)) (Returns-Apple) 4 Pear .40 =Index(A1:A5,Match(Large(B1:B5,4),0)) (Returns-Orange) 5 Peach .50 =Index(A1:A5,Match(Large(B1:B5,5),0)) (Returns-Banana) Is there a way to have the formula in C3 return Pear? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is INDEX,MATCH the best way?
Could it be possible that if some of the cells in column A
(Items) are blank or "" that the division involved would cause this error? Blanks in column A aren't a problem but if you have formula blanks in column B you'll get #VALUE! errors. Try this version that accounts for formula blanks in column B (still an array formula): =INDEX(Item,MATCH(LARGE(IF(ISNUMBER(Cost),Cost-ROW(Cost)/10^10),ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0)) -- Biff Microsoft Excel MVP "M.A.Tyler" <Great Lakes State wrote in message ... OK, now I'm getting a VALUE error. Could it be possible that if some of the cells in column A (Items) are blank or "" that the division involved would cause this error? More importantly is there a fix? "T. Valko" wrote: Try this array formula** : Item = column A Cost = column B =INDEX(Item,MATCH(LARGE(Cost-ROW(Cost)/10^10,ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0)) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "M.A.Tyler" <Great Lakes State wrote in message ... here is the problem: A B C 1 Apple .40 =Index(A1:A5,Match(Large(B1:B5,1),0)) (Returns-Peach) 2 Orange .30 =Index(A1:A5,Match(Large(B1:B5,2),0)) (Returns-Apple) 3 Banana .20 =Index(A1:A5,Match(large(B1:B5,3),0)) (Returns-Apple) 4 Pear .40 =Index(A1:A5,Match(Large(B1:B5,4),0)) (Returns-Orange) 5 Peach .50 =Index(A1:A5,Match(Large(B1:B5,5),0)) (Returns-Banana) Is there a way to have the formula in C3 return Pear? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is INDEX,MATCH the best way?
I think closer, but now a #NUM! error for the blank cells?
"T. Valko" wrote: Could it be possible that if some of the cells in column A (Items) are blank or "" that the division involved would cause this error? Blanks in column A aren't a problem but if you have formula blanks in column B you'll get #VALUE! errors. Try this version that accounts for formula blanks in column B (still an array formula): =INDEX(Item,MATCH(LARGE(IF(ISNUMBER(Cost),Cost-ROW(Cost)/10^10),ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0)) -- Biff Microsoft Excel MVP "M.A.Tyler" <Great Lakes State wrote in message ... OK, now I'm getting a VALUE error. Could it be possible that if some of the cells in column A (Items) are blank or "" that the division involved would cause this error? More importantly is there a fix? "T. Valko" wrote: Try this array formula** : Item = column A Cost = column B =INDEX(Item,MATCH(LARGE(Cost-ROW(Cost)/10^10,ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0)) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "M.A.Tyler" <Great Lakes State wrote in message ... here is the problem: A B C 1 Apple .40 =Index(A1:A5,Match(Large(B1:B5,1),0)) (Returns-Peach) 2 Orange .30 =Index(A1:A5,Match(Large(B1:B5,2),0)) (Returns-Apple) 3 Banana .20 =Index(A1:A5,Match(large(B1:B5,3),0)) (Returns-Apple) 4 Pear .40 =Index(A1:A5,Match(Large(B1:B5,4),0)) (Returns-Orange) 5 Peach .50 =Index(A1:A5,Match(Large(B1:B5,5),0)) (Returns-Banana) Is there a way to have the formula in C3 return Pear? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is INDEX,MATCH the best way?
What version of Excel are you using?
-- Biff Microsoft Excel MVP "M.A.Tyler" <Great Lakes State wrote in message ... I think closer, but now a #NUM! error for the blank cells? "T. Valko" wrote: Could it be possible that if some of the cells in column A (Items) are blank or "" that the division involved would cause this error? Blanks in column A aren't a problem but if you have formula blanks in column B you'll get #VALUE! errors. Try this version that accounts for formula blanks in column B (still an array formula): =INDEX(Item,MATCH(LARGE(IF(ISNUMBER(Cost),Cost-ROW(Cost)/10^10),ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0)) -- Biff Microsoft Excel MVP "M.A.Tyler" <Great Lakes State wrote in message ... OK, now I'm getting a VALUE error. Could it be possible that if some of the cells in column A (Items) are blank or "" that the division involved would cause this error? More importantly is there a fix? "T. Valko" wrote: Try this array formula** : Item = column A Cost = column B =INDEX(Item,MATCH(LARGE(Cost-ROW(Cost)/10^10,ROWS(C$1:C1)),Cost-ROW(Cost)/10^10,0)) Copy down as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "M.A.Tyler" <Great Lakes State wrote in message ... here is the problem: A B C 1 Apple .40 =Index(A1:A5,Match(Large(B1:B5,1),0)) (Returns-Peach) 2 Orange .30 =Index(A1:A5,Match(Large(B1:B5,2),0)) (Returns-Apple) 3 Banana .20 =Index(A1:A5,Match(large(B1:B5,3),0)) (Returns-Apple) 4 Pear .40 =Index(A1:A5,Match(Large(B1:B5,4),0)) (Returns-Orange) 5 Peach .50 =Index(A1:A5,Match(Large(B1:B5,5),0)) (Returns-Banana) Is there a way to have the formula in C3 return Pear? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
| Index or Match or what? | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
Hybrid Mode
