Index,Match,Small

[M.A.Tyler]

"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?

 

[Luke M]

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

 

[RagDyer]

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

 

[M.A.Tyler]

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.

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

 

[RagDyer]

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