Index,Match,Small

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

M.A.Tyler - 28 May 2008 20:40 GMT

"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 to this Message

Luke M - 28 May 2008 20:55 GMT

MATCH type 1 requires the array to be in ascending order, thus creating
problems in your formula.

Signature

Best Regards,

Luke M

> "P" "Q"
> 239 6.073621262 Apple
[quoted text clipped - 15 lines]
>
> Why is this? Shouldn't it return Banana?

Reply to this Message

RagDyer - 28 May 2008 21:11 GMT

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

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> "P" "Q"
> 239 6.073621262 Apple
[quoted text clipped - 17 lines]
>
> Why is this? Shouldn't it return Banana?

Reply to this Message

M.A.Tyler - 28 May 2008 21:31 GMT

Thanks RagDyer, worked like a charm!

> Just change the last "1" in each formula to a "0", to make the formula look
> for an exact match.
[quoted text clipped - 23 lines]
> >
> > Why is this? Shouldn't it return Banana?

Reply to this Message

RagDyer - 30 May 2008 01:22 GMT

You're welcome, and appreciate the feed-back.

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Thanks RagDyer, worked like a charm!
>
[quoted text clipped - 26 lines]
>> >
>> > Why is this? Shouldn't it return Banana?

Reply to this Message