# Thread: Enter numbers and x, y axis Thanks: 0 Likes: 0

1. ## Enter numbers and x, y axis

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

2. ## Re: Enter numbers and x, y axis

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.

3. ## Re: Enter numbers and x, y axis

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

4. ## Re: Enter numbers and x, y axis

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.

5. ## Re: Enter numbers and x, y axis

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.

6. ## Re: Enter numbers and x, y axis

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.

7. ## Re: Enter numbers and x, y axis

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.

8. ## Re: Enter numbers and x, y axis

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

M.

9. ## Re: Enter numbers and x, y axis

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!!!

10. ## Re: Enter numbers and x, y axis

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•