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

	A​	B​	C​	D​	E​	F​
1​
2​		[-1,1]​	[0,1]​	[1,1]​
3​		[-1,0]​	[0,0]​	[1,0]​		[-1,0]​
4​		[-1,-1]​	[0,1]​	[1,-1]​		[8]​
5​
6​		[5]​	[7]​	[6]​
7​		[8]​	[2]​	[3]​
8​		[0]​	[1]​	[7]​
9​

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.