Enter x, y coordinates and displays a number

		Microsoft Office Forums > Microsoft Excel > Excel
Enter x, y coordinates and displays a number

05-11-2019, 10:37 AM

p45cal

Windows 10 Enter x, y coordinates and displays a number

Office 2016

Expert

Join Date: Apr 2014

Posts: 956

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)

In F8:

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)

BOTH Array-Entered, ie. use Ctrl+Shift+Enter to commit the formulae to the sheet and not just Enter.

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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:06 AM.