索引/匹配函数不返回精确匹配。

Trevor_R

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.

Paul Newcome

Exactly what did you select for your ranges?