Reputation: 29
There have been some answers regarding this subject, but none seem to be robust enough with the introduction of new data.
I have a table of values, along with an x, and z axis. The values in the middle represent the 3rd axis which form a surface plot (just additional information), y as seen in the image below:
Cell P3 has the following formula to attempt to find the nearest value, y within the table of values.
=VLOOKUP(O2,C2:L12,1,TRUE)
It incorrectly locates a value which on observation is not the nearest value.
My question is, is there a more robust way of finding the nearest value given an input O3.
Additionally, once that value has been located. Would it be possible to find the corresponding z-axis value (values in green), which aligns to the located value from VLOOKUP?
This is a mock-up to illustrate my request:
Many thanks,
Upvotes: 0
Views: 386
Reputation: 46
I would use index + match. Vlookup/Hlookup is for when you have an array with only 2 axis
here's an example of how to do it:
the formula I used to get the Z value: =INDEX(B3:G8,MATCH(J5,B3:B8,0),MATCH(I5,B3:G3,0))
Upvotes: 1