Finding a value with horizontal and vertical parameters

[willian2142]

Hi everyone,

I need to find a value on line 2, then it needs to find the value on the vertical column, and finally the option correspondent to that value.
In the image bellow it's an exemple of what i need.

 

[Scott T]

This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=INDEX(A3:A6,MATCH(B10,INDEX(B3:G6,0,MATCH(B9,B2:G2,0)),0))

 

[Marcelo Branco]

Welcome to Mr Excel

Try
=INDEX($A$3:$A$6,MATCH(C10,INDEX($B$3:$G$6,0,MATCH(C9,$B$2:$G$2,0)),0))
confirmed with just Enter

M.

 

[willian2142]

This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=INDEX(A3:A6,MATCH(B10,INDEX(B3:G6,0,MATCH(B9,B2:G2,0)),0))

Ok, that works, but now, how can i find the closest highest value? Like, if the Horizontal is 3,8 and vertical 33

 

[Scott T]

=INDEX(A3:A6,MATCH(B10,INDEX(B3:G6,0,MATCH(B9,B2:G2,[COLOR=#ff0000]0[/COLOR])),[COLOR=#ff0000]0[/COLOR]))

The zero in the match formulas are the match type change to match what you want
[TABLE="class: collapse, width: 99%"]
<tbody>[TR="class: trbgodd"]
[TD="class: noborder, align: left"]1 or omitted
[/TD]
[TD="class: noborder, align: left"]MATCH finds the
largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order,
for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.Zero
[/TD]
[/TR]
[TR="class: trbgeven"]
[TD="class: noborder, align: left"]Zero(0)[/TD]
[TD="class: noborder, align: left"]MATCH finds the
first value that is exactly equal to lookup_value. The
values in the lookup_array argument can be in any
order.
[/TD]
[/TR]
[TR="class: trbgodd"]
[TD="class: noborder, align: left"]-1
[/TD]
[TD="class: noborder, align: left"]MATCH finds the
smallest value that is greater than or equal to
lookup_value. The values in the lookup_array
argument must be placed in descending order, for example: TRUE, FALSE, Z-A,
...2, 1, 0, -1, -2, ..., and so on.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[willian2142]

Yes, but when i change both numbers to "-1" it doesn't work. I get a #N/D

 

[Scott T]

Use of 1 requires the data to be shorted in Ascending order and -1 requires the data be in descending order. You would have to reorder the data to be in descending order to use -1
[TABLE="class: grid, width: 493"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]D[/TD]
[TD="align: right"]E[/TD]
[TD="align: right"]F[/TD]
[TD="align: right"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]option 1
[/TD]
[TD="align: right"]37
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]39
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]41
[/TD]
[TD="align: right"]42
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]option 2
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]36
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]option 3
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]27
[/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]option 4
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]21
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"]25
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]har
[/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]vert
[/TD]
[TD="align: right"]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]corresponding option
[/TD]
[TD]option 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[willian2142]

You´re the king!