Extracting data from table?

[X16]

Hi All,

I have a question on how to extract a certain value from a table.

pic.JPG
As above image, the purpose is to key in a formula in cell B6 that will retrieve the corresponding value base on the criteria in cell B3 and cell B4.

For example, the value corresponding to A2 and B3 is 0.8

I have tried using If, AND, OR, lookup, index, match functions but keep getting errors.
Please advise how to obtain the corresponding data in cell B6, when i input in cell B3 and B4.

Thank all.

[José Augusto]

Try

Please Login or Register  to view this content.

[Phuocam]

Or try this...

=VLOOKUP(B4,$E$3:$L$10,MATCH(B3,$E$2:$L$2,0),0)

[X16]

Both answer works fine! thanks Jose and phuocam

[Phuocam]

You're welcome.

[X16]

Hi again,

Just an extended question to the previous one. can i include interpolation into the equation?

for example, As per revised excel (attached to this post), how do i get a
corresponding value (cell B6) for value 3.5 (column E & Cell B4) and A2 (Cell B3).

Note:Column E will be in ascending numerical value, but Row 2 will NOT be pure numerical value.

Appreciate if anyone can help on this. Thanks!

[Phuocam]

Try this ...

=FORECAST(B4,OFFSET($E$2,MATCH(B4,$E$3:$E$9,1),MATCH(B3,$F$2:$L$2,0),2,1),OFFSET($E$2,MATCH(B4,$E$3:$E$9,1),,2,1))

[José Augusto]

Hi
@Phuocam uses a linear interpolation selecting two points.
If you want uses all the information of the column select A1 or A2, ... you must use a polinomial of degree 7 (8 points)
To do that use the following formula

Please Login or Register  to view this content.

See the file Book1 (2) (1).xlsx for clarification