FAQ - Table Intersect

PROGRAMMING MANAGEMENT		A resource for users and developers of Microsoft Excel	Site Map	Search


			Email us your FAQ suggestion.

			Visit often ! Pages are updated daily.
	This is the Excel "How to" Frequently Asked Questions page
Answers to Frequently Asked Questions about how to do common tasks in Microsoft Excel.

The World of Atlas

Home

Excel Solutions and Examples

Access & Excel	Add-Ins	API	Charts	Class Modules	Comments	Conditional Formatting	Control Toolbox
Data Validation	Date & Time	Dialog Sheets	Edit	Errors	Events	Excel Fact & Fiction	Excel Object
Filter	Find / Replace	Format	Forms Toolbar	Formulas	Glossary	Hyperlinks	Internet & Excel
Keyboard	Menu Bar	Mouse	OLAP Cubes	Outlook & Excel	Pivot Tables	PowerPoint & Excel	Print
Ranges	Ribbon	Sheets	Sort	SQL	Tips & Tricks	User Defined Functions	UserForms
VBA & the VBE	VBA Magic	Window	Windows	Word & Excel	Workbooks	XLM "4.0 Macros"	XML

Excel Community

Excel "How to" FAQs

List of Excel "How to" FAQs with links to their answers.

Data entry: How to...		Formulas for sum, count, lookup, last entry: How to...		Worksheets and workbooks: How to...
	...identify duplicate entries in a range?		...return the last number and its cell address?		...display the worksheet tab name in a cell?
	...prevent duplicate entries in a range?		...return the last text and its cell address?		...display the workbook name in a cell?
	...delete duplicate entries in a range?		...return the last any type entry and its cell address?		...display the workbook path in a cell?
	...force letters to be UPPER CASE?		...return a table's intersecting row & column value?		...display the workbook path and name in a cell?
	...limit how many characters can be entered?		...return the second highest or lowest number?		...display the workbook path, name, sheet in a cell?
	...establish the "carriage return" affect in a cell?		...average without zeros?		...name a worksheet tab based on a cell value?
	...fill empty cells in a column with value from above?		...sum, average, or count colored cells?		...change column headers from numbers to letters?
	...stop Excel from converting an entry into a date?		...sum, average, or count to omit Min or Max?		...verify if a certain worksheet exists; create if not?
	...automatically insert colon : when entering a time?		...sum, average, or count across multiple sheets?		...verify if a certain workbook exists?
	...enter a number with a visible leading zero?		...sum, average, or count visible or unfiltered cells?		...verify if a certain workbook is open?
			...sum, average, or count with multiple conditions?		...add more rows or columns to my worksheet?
Data manipulation, extraction, parsing: How to...			...avoid #DIV/0!, #N/A, and other formula errors?		...know how many sheets my workbook can hold?
	...separate alphanumeric numbers and letters?		...use VLOOKUP to return value in column to left?		...sort sheets by tab name in alphanumeric order?
	...separate first and last names into their own cell?				...open a workbook to a certain sheet and cell?
	...extract all to the left of a certain character?	Programming Visual Basic for Applications: How to...			...prevent workbook being renamed or distributed?
	...extract all to the right of a certain character?		...create, install, and run a macro?		...save or export my workbook as an .exe file?
	...move negative sign from back to front of number?		...create, install, and utilize a User Defined Function?		...keep rows or columns visible when scrolling?
	...sort a list by more than three column fields?		...create, install, and utilize Event code?		...make a drop-down list for users to select sheets?
	...convert numeric values to their equivalent words?		...create, install, and utilize Class Module code?		...know when the workbook was created?
	...add or subtract a day, month, or year of a date?		...force another computer to enable my macros?		...know when the workbook was modified or saved?
	...delete rows where empty cells exist in a column?		...stop the screen movements while macros run?		...open all workbooks, modify them, then close all?
	...delete hyperlinks but keep their underlying text?		...close a workbook without the prompt to save it?		...recover or undo a worksheet I wrongly deleted?
			...delete a sheet without the prompt to confirm it?		...make a workbook expire by deleting itself?
Data Validation: How to...			...save as existing name without prompt to confirm?		...convert each worksheet into its own workbook?
	...make the cell's drop-down arrow always visible?		...deal with a Macro warning when no macros exist?
	...type the first letter to select that part of the list?		...create a Message Box timed to click OK by itself?	XL Zone: Assorted questions...
	...adjust the drop-down list's size, width, or font?		...establish a "carriage return" line in my MsgBox?		Why Excel asks to save when nothing changed?
	...have more than 8 visible drop down list items?		...display a progress bar while my macro runs?		Why Excel asks to update links but there are none?
	...have one list dependent on another list?		...copy to or go to the next available row or column?		Why the 'Too many different cell formats' message?
			...locate the first empty cell in a row or column?		How do I make the Reviewing Toolbar go away?
Format: How to...			...know the last row for one or more columns?		*How do I find wildcard characters "?" and "" ?**
	...conditionally format for more than 3 conditions?		...know the last column for one or more rows?		How do I keep a button always visible on the sheet?
	...colorize the active cell, row, or column?		...know the address of the sheet's used range?		How do I autoclose a workbook after inactivity?
	...format a date when concatenating it with text?		...know the address of the sheet's data range?		How can I put a running clock on my spreadsheet?
	...make a font invisible so a cell's value is not seen?		...select a range of unknown size or location?		How do I ask for input of an unambiguous date?
	...make a cell's value flash or blink to get attention?		...fill a formula down to the last used row?		How do I customize the buttons on a Message Box?
	...format a formula's returned value characters?		...find multiple entries of the same value in a range?		How do I show a MsgBox in a corner, not centered?
	...format the sum of times for hours and minutes?		...get the user name of the computer or network?		What can I do to learn more about Excel and VBA?

How to return a table's intersecting row & column value?

Example

Intersecting values

Formulas

The pictured example shows a table with row headers in column A (salesperson names), and column headers in row 1 (month names).

Because there are three components to the table (row headers, column headers, and intersecting number values), there are three scenarios you may be faced with, where knowing two of the components will allow you to derive the unknown third component.

Scenario #1 - the unknown Sales Number (the intersecting value)
In cell D13, the unknown Sales Number is returned by knowing the Salesperson in cell D11, and the Month of sale in cell D12, with this formula:
=INDEX($A$1:$M$6,MATCH($D$11,$A$1:$A$6,0),MATCH($D$12,$A$1:$M$1,0))

Scenario #2 - the unknown Month (column header values in row 1)
In cell I13, the unknown Month of sale is returned by knowing the Sales Number in cell I11, and the Salesperson in cell I12, with this formula:
=INDEX($A$1:$M$1,MATCH($I$11,INDEX($A$2:$M$6,MATCH($I$12,$A$2:$A$6,0),0),0))

Scenario #3 - the unknown Salesperson (row header values in column A)
In cell N13, the unknown Salesperson is returned by knowing the Month of sale in cell N11, and the Sales Number in cell N12, with this forrmula:
=INDEX($A$2:$A$6,MATCH($N$12,INDIRECT(ADDRESS(2,MATCH($N$11,$A$1:$M$1,0))&":"&ADDRESS(6,MATCH($N$11,$A$1:$M$1,0))),0),1)

Top of Page

List of Excel "How to" FAQs

Home

About Atlas

Excel Consulting

Excel Training

Excel Testing

Atlas Store

Contact Atlas