Forum Discussion
Returning row and column value in a large chart if column is in a range
Second MATCH could be like
=MATCH(W3,D3:R3,-1)+1
Second MATCH could be like
=MATCH(W3,D3:R3,-1)+1
This was the fix. Thanks a bunch. Why does this work versus what i was doing?
Orin, you used zero as third parameter for MATCH, which means exact match. If exact match isn't found the function returns #N/A error.
Since you horizontal axis numbers are sorted in descending order you may use -1 as third parameter, function returns position of the value closest from the left to your lookup value (Greater than).
You may check
https://support.office.com/en-us/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
https://exceljet.net/excel-functions/excel-match-function
Many thanks to you good sir for helping me out on this. Now its on to referencing this workbook from another one whilst changing the lookup cells to the other workbook. No doubt a mere drop in the bucket for one such as yourself. :) Thanks again.
