Index Match Returning the Wrong Value

Nick Allgauer

I'm perplexed why the Index(Match() formula might be returning the wrong value for me. I've checked this at least a dozen times and I have no idea. Please see screenshot below:

=INDEX({Sell Rate}, MATCH(Position@row, {Position}, 0)

It should be matching the sell rate in this other sheet when the position is Test. As you can clearly see, the value in Test is $5.00 and it's matching $0.00.

The Buy Rate is doing the same thing.

I have one called RN on here and it IS returning the correct value. If I rename RN as Test so it's in the first 15 rows and rename Test as RN, Test does work and RN doesn't.

Is there a reason why it might only be recording the first 15 rows??

bcwilson.ca

I think you have it Messed up..

you pasted.. INDEX({Sell Rate}, MATCH(Position@row, {Position}, 0)

But your formula has INDEX({Sell Rate}, MATCH(Position@row, {Position}), 0).

The bracket "}), 0" placed where it causes the "0" to be referenced as the column_index for the INDEX function not the search_type for the MATCH Function

This will cause the MATCH to be done in the default "1", which is a sorted list..

It sees TL and says oh well Te is before TL so I will use $0.00

Try changing it to : INDEX({Sell Rate}, MATCH(Position@row, {Position}, 0))

Probably just too long staring at ({[Different Brackets]})

bcwilson.ca

I think you have it Messed up..

you pasted.. INDEX({Sell Rate}, MATCH(Position@row, {Position}, 0)

But your formula has INDEX({Sell Rate}, MATCH(Position@row, {Position}), 0).

The bracket "}), 0" placed where it causes the "0" to be referenced as the column_index for the INDEX function not the search_type for the MATCH Function

This will cause the MATCH to be done in the default "1", which is a sorted list..

It sees TL and says oh well Te is before TL so I will use $0.00

Try changing it to : INDEX({Sell Rate}, MATCH(Position@row, {Position}, 0))

Probably just too long staring at ({[Different Brackets]})

Nick Allgauer

Eureka! Thanks a ton for finding this error. 🤩

pris

@bcwilson.ca You're a lifesaver! This really helped me!

Derrick P

Ha, old post, still a good tip… worked on it last night, eyes got tired, tried again with fresh eyes this morning. Still couldn't get it. Then found this. Thanks for sharing.