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?

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?

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?

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?

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?