#1




Enter x, y coordinates and displays a number
Range B2D4:
[1,1] [0,1] [1,1] Example: [1,0] [0,0] [1,0] Cell F3 → [0,1] (ENTER X,Y) [1,1] [ 0,1] [1,1] Cell F4 → [7] (RESULTS) Range B6D8: [5] [7] [6] Example: [8] [2] [3] Cell F7 → [0] (ENTER #) [0] [1] [7] Cell F8 → [1,1] (RESULTS) Would you please be so kind as to show me how to create a formula after entering any (x,y) coordinates in Cell F3, it will automatically display the corresponding number in Cell F4? Also, vice versa, where I would enter any number in Cell F7 and automatically display the corresponding (x,y) coordinates in Cell F8? Please note: Using MS Office 365. The (x,y) coordinates in range B2D4 corresponds to the numbers in range B6D8 I just made it 3x3 matrix to make it easier to understand what I needed to be done. Thanks! 
#2




In F4:
Code:
=INDEX(B6:D8,SUMPRODUCT((B2:D4=F3)*ROW(B2:D4))ROW(B2:D4)+1,SUMPRODUCT((B2:D4=F3)*COLUMN(B2:D4))COLUMN(B2:D4)+1) Code:
=INDEX(B2:D4,SUMPRODUCT((B6:D8=F7)*ROW(B6:D8))ROW(B6:D8)+1,SUMPRODUCT((B6:D8=F7)*COLUMN(B6:D8))COLUMN(B6:D8)+1) 
#3




enter x,y to display a number
I had to revised my data. 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:
Range: A1:F3 graph: cell A1 is 1cell B1 is 1cell C1 is 0cell D1 is 1cell E1 is 1cell F1 is 1 cell A2 is 1cell B2 is 0cell C2 is 0cell D2 is 1cell E2 is 1cell F2 is 0 cell A3 is 1cell B3 is 1cell C3 is 0cell D3 is 1cell E3 is 1cell F3 is 11 Range: A5:C7 cell A5 is 1cell B5 is 2cell C5 is 3 cell A6 is 4cell B6 is 5cell C6 is 6 cell A7 is 7cell B7 is 8cell 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. 
#4




in cell I4, arrayenter:
Code:
=MIN(IF((($A$1:$C$3=$H$2)*($D$1:$F$3=$I$2))=1,$A$5:$C$7)) edit post posting: It's probably wrong. Try instead, still arrayentered: Code:
=MIN(IF((CHOOSE({1,2,3},$A$1:$A$3,$C$1:$C$3,$E$1:$E$3)=$H$2)*(CHOOSE({1,2,3},$B$1:$B$3,$D$1:$D$3,$F$1:$F$3)=$I$2)=1,$A$5:$C$7)) 
#5




enter x,y to display a number
My mind feels like there's a ball bouncing back and forth inside my head. Correction on data. Sorry for the errors. Cell D1 is 0 and Cell F3 is 1.

#6




Crossposted at: https://www.mrexcel.com/forum/excel...xyaxis.html

#7




updated response in msg#4

#8




If I enter the values for H2 and I2 these are my results:
H2,I2 1,1 Correct! 1 1,0 Correct! 4 1,1 Correct! 7 0,1 Incorrect 3, Answer should be 2 0,0 Incorrect 6, Answer should be 5 0,1 Incorrect 9, Answer should be 8 1,1 Incorrect 2, Answer should be 3 1,0 Incorrect 0, Answer should be 6 1,1 Incorrect 0, Answer should be 9 I tried revising the formula but I couldn't figure it out. Would you please be so kind as to show me what can I do to the current formula so it will work? 
#9




I used your updated formula! You are utterly AWESOME!! It worked beautifully. Now, I can go to sleep. Thanks a million!!!

#10




arthurz11: For crossposting etiquette, please read http://www.excelguru.ca/content.php?184
__________________
Cheers, Paul Edstein [MS MVP  Word] 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
xref to heading 5.1 stubbornly displays 0 as heading number  eNGiNe  Word  4  06172018 11:05 PM 
enter a constant based on entered number  So New2 This  Word VBA  1  10172014 05:29 AM 
Enter a number in a locked equation within a cell that then calculates  Carchee  Excel  7  06132014 11:11 AM 
VBA code to read number of footnote and enter in text  rekent  Word VBA  2  05132014 06:53 AM 
Enter Number on any sheet one time only.  paulrm906  Excel  1  04282006 07:35 AM 