Enter numbers and x, y axis

arthurz11

New Member
Joined
Nov 9, 2007
Messages
23
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

New Member
Joined
Nov 9, 2007
Messages
23
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

New Member
Joined
Nov 9, 2007
Messages
23
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

New Member
Joined
Nov 9, 2007
Messages
23
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.
 

arthurz11

New Member
Joined
Nov 9, 2007
Messages
23
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,784
Messages
5,488,860
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top