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]

Try

F4
=INDEX(B6:D8,AGGREGATE(14,6,(ROW(B2:D4)-ROW(B2)+1)*(B2:D4=F3),1),AGGREGATE(14,6,(COLUMN(B2:D4)-COLUMN(B2)+1)*(B2:D4=F3),1))

F8
=INDEX(B2:D4,AGGREGATE(14,6,(ROW(B6:D8)-ROW(B6)+1)*(B6:D8=F7),1),AGGREGATE(14,6,(COLUMN(B6:D8)-COLUMN(B6)+1)*(B6:D8=F7),1))

Hope this helps

M.

 

[arthurz11]

Thanks Marcelo!
Wow! Very nice work! However, the formula in Cell F4 gives me an error when I enter [-1,1] [-1,0] and [-1,-1] in Cell F3. I did make an error on Cell B2, which should of been [-1,1] instead [1,1]. So this is an error on my part and not yours. So what must I do to revised the formula in Cell F4 to make it work correctly?
Very appreciatively,
Arthur

 

[Marcelo Branco]

I assumed you should enter only one value in F3, not multiple values: "when I enter [-1,1] [-1,0] and [-1,-1] in Cell F3"

Could you clarify?

M.

 

[arthurz11]

It works good when I enter the coordinates 1,1 in Cell F3 and it displays the number 6 in Cell F4.

But when I enter the coordinates -1,0 in Cell F3, it gives me an error in Cell F4 when it should display the number 8 instead but it does not.

Formula in Cell F7 works great!!! This is where I do enter one value. I entered the number 7 in Cell F7 and it displays the coordinates 1,-1 in Cell F8.

 

[Marcelo Branco]

It worked perfectly for me


[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][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

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

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

2​

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

[-1,1]​

[/td][td]

[0,1]​

[/td][td]

[1,1]​

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

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

3​

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

[-1,0]​

[/td][td]

[0,0]​

[/td][td]

[1,0]​

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

[-1,0]​

[/td][/tr]

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

4​

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

[-1,-1]​

[/td][td]

[0,1]​

[/td][td]

[1,-1]​

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

[8]​

[/td][/tr]

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

5​

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

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

6​

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

[5]​

[/td][td]

[7]​

[/td][td]

[6]​

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

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

7​

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

[8]​

[/td][td]

[2]​

[/td][td]

[3]​

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

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

8​

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

[0]​

[/td][td]

[1]​

[/td][td]

[7]​

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

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

9​

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

Formula in F4 (see post 2)

If it's not working for you, check if the cell F3 is equal to B3 - try in an empty cell
=B3=F3

If this return FALSE probably there is an extraneous space either in B3 or in F3

M.

 

[arthurz11]

I did not see any extraneous spaces in Cells B3 or in F3. I did received the FALSE when I typed =B3=F3. I tried all the other cells and I got the same FALSE too but they worked! I even created a new spreadsheet and I still got the errors if I entered either of these three coordinates: (-1,0) (-1,1) or (-1,-1). It looks like it will not accept the first negative numbers on cells B2, B3, B4 in Cell F3. I don't know why. I will not display any of these numbers: 5, 8,or 0. I only get an error dialog box. I was analyzing both formulas and the only thing I did not understand is that what does the numbers 14,6 mean? I hope I can find out what is wrong.

 

[Marcelo Branco]

You should enter [-1,0] that is: using square brackets, not (-1,0)

M.

 

[arthurz11]

It works perfect!!! I was not entering the apostrophe ‘ before -1,0. I don’t have to enter the parentheses ( ) nor the brackets [ ] for it to work. It worked all along. But it’s funny that if the coordinates first digits are negative like -1,1 or -1,-1 or -1,0 and “without” typing the apostrophe ‘ in front, it will not work. Thank you so much. I apologized for any inconvenience. It works beautifully!!!

 

[arthurz11]

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