CODEWITHSUNDEEP
excelfunctionindexingexcel-formulavlookup
Idrawthings
Idrawthings

Reputation: 29

VLOOKUP to Nearest Value in Table

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:

enter image description here

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:

enter image description here

Many thanks,

Upvotes: 0

Views: 386

Answers (1)

Maya Amiad
Maya Amiad

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:

enter image description here

the formula I used to get the Z value: =INDEX(B3:G8,MATCH(J5,B3:B8,0),MATCH(I5,B3:G3,0))

Upvotes: 1

Related Questions