Index Match Returning the Wrong Value

Options

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

Best Answer

  • bcwilson.ca
    bcwilson.ca ✭✭✭✭✭
    Answer ✓
    Options

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

Answers

  • bcwilson.ca
    bcwilson.ca ✭✭✭✭✭
    Answer ✓
    Options

    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
    Nick Allgauer ✭✭✭✭
    Options

    Eureka! Thanks a ton for finding this error. 🤩

  • pris
    pris ✭✭✭✭
    Options

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

  • Derrick P
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!