INDEX/MATCH function doesn't return exact match.
I'm having and issue with the MATCH function not returning an exact match. If my understanding of the formula is correct:
"1" for the [search_type] should find the first value greater than or equal to the search_value starting from left to right, or top to bottom.
"-1" should find the first value less than or equal to the search_value starting from right to left, or bottom to top.
"0" should return an exact match ...However when I type "0" it is not working properly.
Formula I'm using =INDEX({8. Base Pricing - Release 8 Range 4}, MATCH([Account Codes]@row, {Account Code}, 0), MATCH([Unit Type]1456, {8. Base Pricing - Release 8 Range 5}, 0))
The goal is to match the Account Code (blue) with the correct row, then the Unit Type (pink) with the correct column, and return the corresponding price.
This is the sheet INDEX is referencing, with Cost Code as the row range, and row 1 as the column range. My example should be returning a value of $1,115,000, but it is returning a value of $1,130,000
So for whatever reason it's clearly pulling the value from column 3M-D instead of the exact match of "3M". I understand that with "1" and "-1" it will pull a partial match with the first "3M" it comes across, but when "0" is supposed to be an exact match it's still not working correctly? For the record I've run across this issue before when using the [column_index] with the INDEX function, where sometimes it works and sometimes it doesn't.
Thanks.
Answers
-
Exactly what did you select for your ranges?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!