Nov 19 2018 09:41 AM
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.
Nov 19 2018 10:06 AM
Nov 19 2018 10:48 AM
SolutionSecond MATCH could be like
=MATCH(W3,D3:R3,-1)+1
Nov 19 2018 11:09 AM
This was the fix. Thanks a bunch. Why does this work versus what i was doing?
Nov 19 2018 12:31 PM
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
Nov 19 2018 01:00 PM
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.
Nov 19 2018 10:48 AM
Solution