SOLVED

Returning row and column value in a large chart if column is in a range

Copper Contributor

Chart Value.JPG

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.

 

5 Replies
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!!
best response confirmed by Orin Huff (Copper Contributor)
Solution

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.

1 best response

Accepted Solutions
best response confirmed by Orin Huff (Copper Contributor)
Solution

Second MATCH could be like

=MATCH(W3,D3:R3,-1)+1

View solution in original post