Forum Discussion
Returning row and column value in a large chart if column is in a range
I'm wondering if anyone can help me with this. This is for a wastewater oxygen saturation chart used in determining Biochemical Oxygen Demand values. I want to find the intersection value returned to a cell of my choosing. But the catch is that the column values are in a range from the column farthest out value is the low number, to the previous column value being the lowest range to jump into the next column of values. I've tried VLOOKUP, coupled with INDEX / MATCH, and I still can't seem to get a handle on this. I tried
=INDEX(D4:R33,MATCH(V3,C4:C33,0),MATCH(W3,D3:R3,0))
But as with cell U1 formula, it returns N/A. I'm not sure how to get the top column numbers to return using the numbers in V3 and W3. So I would need the formula to return 8.77 or cell K27.
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
- Hi Orin,
The Index-Match formula you proposed looks as if it should work. I would manually test to ensure that the values are actually matching. For example, use another free cell to ensure that V3 is equal to C27, by just entering "=V3=C27"
If this checks out for both the values you are attempting to match, try debugging the pieces of your formula by selecting each argument of the Index function and evaluating in the formula editor by pressing F9. This can help you determine where the actual error is occuring in nested functions.
Hope this helps!!
