Enter numbers and x, y axis

[arthurz11]

Would you please be so kind as to show me how to create a formula in Cell F3 after entering any (x,y) coordinates from range B2:D4 and then it will automatically display the corresponding number in Cell F4?

Also, vice versa, how to create a formula in Cell F7 and enter any numbers from range B6:D8 and automatically display the corresponding (x,y) coordinates in Cell F8?

Please note: Using MS Office 365. The (x,y) coordinates in range B2:D4 corresponds to the numbers in range B6:D8

I just made it 3x3 to make it easier
to understand what I needed to be done. Thanks!

Please view the following data:

Range B2:D4
[1,1] [0,1] [1,1] Example: x, y coordinates
[-1,0] [0,0] [1,0] Cell F3 →[0,1] ENTER X,Y
[-1,-1] [0,-1] [1,-1]Cell F4 →[7] RESULTS

Range B6:D8
[5] [7] [6] Example: numbers
[8] [2] [3] Cell F7 →[0] ENTER #
[0] [1] [7] Cell F8 →[-1,-1] RESULTS

 

[Marcelo Branco]

But what does the 14,6 means in the formula?? I still would like to know.

See the help about AGGREGATE function

14 = LARGE
6 = Ignore errors

M.

 

[arthurz11]

Thanks for all your help!!! Solution solved!!!!

 

[arthurz11]

In what I am trying to do is that I have to create the problem differently because I need to use two cells for x,y coordinates instead of one cell as you previously helped. The objective is to enter the x,y coordinates in cells H2 and I2 and it displays the corresponding value 7 in cell I4. This is the following DATA:

cell A1 is -1---cell B1 is 1---cell C1 is 0---cell D1 is 1---cell E1 is 1---cell F1 is 1
cell A1 is -1---cell B1 is 0---cell C1 is 0---cell D1 is 1---cell E1 is 1---cell F1 is 0
cell A1 is -1---cell B1 is -1---cell C1 is 0---cell D1 is -1---cell E1 is 1---cell F1 is -11

cell A5 is 1---cell B5 is 2---cell C5 is 3
cell A6 is 4---cell B6 is 5---cell C6 is 6
cell A7 is 7---cell B7 is 8---cell C7 is 9

In cell H2 I enter the -1 and in cell I2 I enter -1 (This is the x,y coordinates that I entered)

In cell I4 the formula finds the number and displays the 7
1 2 3
4 5 6
7 8 9

I want to input the x,y coordinates in two cells H2 and I2 in stead of one as previously. So on this one I have cells H2 and I2.
The formula in cell I2 automatically puts the assigned perspective value.

 

[arthurz11]

Oops! I had to correct the data. Sorry. Her is the corrected one.

Range: A1:F3 graph:
cell A1 is -1---cell B1 is 1---cell C1 is 0---cell D1 is 1---cell E1 is 1---cell F1 is 1
cell A2 is -1---cell B2 is 0---cell C2 is 0---cell D2 is 1---cell E2 is 1---cell F2 is 0
cell A3 is -1---cell B3 is -1---cell C3 is 0---cell D3 is -1---cell E3 is 1---cell F3 is -11

Range: A5:C7
cell A5 is 1---cell B5 is 2---cell C5 is 3
cell A6 is 4---cell B6 is 5---cell C6 is 6
cell A7 is 7---cell B7 is 8---cell C7 is 9

 

[Marcelo Branco]

Is this correct:
C1=0 D1=1
C2=0 D2=1
(same coordinates ???)

F3 = -11 (???)

M.

 

[arthurz11]

Thinking of too many numbers is like having a bouncy ball inside my head. Sorry. Nope, my error. D2 is 0. You have a keen eye. Thanks for catching that.

 

[arthurz11]

F3 is just -1. Sigh!! I have not had enough sleep. Sorry again.

 

[Marcelo Branco]

Maybe something like this (i've created two formulas, Row and Column, to make things easier)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

-1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][td]

x​

[/td][td]

y​

[/td][td]

Row​

[/td][td]

Column​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td][/td][td]

-1​

[/td][td]

-1​

[/td][td]

3​

[/td][td]

1​

[/td][td]

7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

0​

[/td][td]

-1​

[/td][td]

1​

[/td][td]

-1​

[/td][td][/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

3​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td]

0​

[/td][td]

2​

[/td][td]

2​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td]

-1​

[/td][td]

3​

[/td][td]

3​

[/td][td]

9​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Coordinates (x,y) in columns H:I

Formula in J2 copied down
=IFERROR(AGGREGATE(14,6,(ROW($A$1:$E$3)-ROW($A$1)+1)/(($A$1:$E$3=H2)*($B$1:$F$3=I2)*(MOD(COLUMN($A$1:$E$3)-COLUMN($A$1)+1,2)=1)),1),"Not found")

Formula in K2 copied down
=IFERROR(AGGREGATE(14,6,(COLUMN($A$1:$E$3)-COLUMN($A$1)+2)/(($A$1:$E$3=H2)*($B$1:$F$3=I2)*(MOD(COLUMN($A$1:$E$3)-COLUMN($A$1)+1,2)=1)),1)/2,"Not Found")

Formula in L2 copied down
=IF(COUNT(I2:J2)=2,INDEX($A$5:$C$7,J2,K2),"Not found")

M.

 

[Macropod]

Cross-posted at: http://www.msofficeforums.com/excel/42445-enter-x-y-coordinates-displays-number.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html