| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index,Match,Small
"P" "Q"
239 6.073621262 Apple 240 2.515599192 Banana 241 35.09606534 Wood 242 8.047250091 Orange 243 14.19618029 Grape 244 2.409451009 Mellon 245 12.01403392 Pumpkin 246 247 248 249 250 =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,1),Sheet1!$P$239:$P$250,1)) Returns, Mellon. =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,2),Sheet1!$P$239:$P$250,1)) Returns, Mellon Too? Why is this? Shouldn't it return Banana? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index,Match,Small
MATCH type 1 requires the array to be in ascending order, thus creating
problems in your formula. -- Best Regards, Luke M "M.A.Tyler" wrote: "P" "Q" 239 6.073621262 Apple 240 2.515599192 Banana 241 35.09606534 Wood 242 8.047250091 Orange 243 14.19618029 Grape 244 2.409451009 Mellon 245 12.01403392 Pumpkin 246 247 248 249 250 =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,1),Sheet1!$P$239:$P$250,1)) Returns, Mellon. =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,2),Sheet1!$P$239:$P$250,1)) Returns, Mellon Too? Why is this? Shouldn't it return Banana? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index,Match,Small
Just change the last "1" in each formula to a "0", to make the formula look
for an exact match. =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,1),Sheet1!$P$239:$P$250,0)) =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,2),Sheet1!$P$239:$P$250,0)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "M.A.Tyler" <Great Lakes State wrote in message ... "P" "Q" 239 6.073621262 Apple 240 2.515599192 Banana 241 35.09606534 Wood 242 8.047250091 Orange 243 14.19618029 Grape 244 2.409451009 Mellon 245 12.01403392 Pumpkin 246 247 248 249 250 =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,1),Sheet1!$P$239:$P$250,1)) Returns, Mellon. =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,2),Sheet1!$P$239:$P$250,1)) Returns, Mellon Too? Why is this? Shouldn't it return Banana? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index,Match,Small
Thanks RagDyer, worked like a charm!
"RagDyer" wrote: Just change the last "1" in each formula to a "0", to make the formula look for an exact match. =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,1),Sheet1!$P$239:$P$250,0)) =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,2),Sheet1!$P$239:$P$250,0)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "M.A.Tyler" <Great Lakes State wrote in message ... "P" "Q" 239 6.073621262 Apple 240 2.515599192 Banana 241 35.09606534 Wood 242 8.047250091 Orange 243 14.19618029 Grape 244 2.409451009 Mellon 245 12.01403392 Pumpkin 246 247 248 249 250 =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,1),Sheet1!$P$239:$P$250,1)) Returns, Mellon. =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,2),Sheet1!$P$239:$P$250,1)) Returns, Mellon Too? Why is this? Shouldn't it return Banana? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index,Match,Small
You're welcome, and appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "M.A.Tyler" <Great Lakes State wrote in message ... Thanks RagDyer, worked like a charm! "RagDyer" wrote: Just change the last "1" in each formula to a "0", to make the formula look for an exact match. =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,1),Sheet1!$P$239:$P$250,0)) =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,2),Sheet1!$P$239:$P$250,0)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "M.A.Tyler" <Great Lakes State wrote in message ... "P" "Q" 239 6.073621262 Apple 240 2.515599192 Banana 241 35.09606534 Wood 242 8.047250091 Orange 243 14.19618029 Grape 244 2.409451009 Mellon 245 12.01403392 Pumpkin 246 247 248 249 250 =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,1),Sheet1!$P$239:$P$250,1)) Returns, Mellon. =INDEX(Sheet1!$Q$239:$Q$250,MATCH(SMALL(Sheet1!$P$ 239:$P$250,2),Sheet1!$P$239:$P$250,1)) Returns, Mellon Too? Why is this? Shouldn't it return Banana? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ISERROR,SMALL,INDEX, MATCH, SMALL?? | Excel Discussion (Misc queries) | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Vlookup/Match/Index/Small - I think | 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 | |||
Linear Mode
