Index Match Returning the Wrong Value

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

Find more posts tagged with

Accepted answers

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.