Ask a question »

Excel - Define ranges and find the value at row-column intersection

November 2015

Excel - Define ranges and find the value at row-column intersection

The aim is to find the value at the intersection of a row and a column and designate them by a common name to perform this search.

So let's take a table with distances between cities, the columns are called "lyon" and marseille and the rows are "paris" and lille", and we want to get the distances between two towns simply by writing their name.

Automatically name the rows and columns of a table

  • In our example, the table occupies the range A3:C5.
  • Select the range and go to "Formulas / Define Names".
  • Choose "From Selection" keeping the default options: "Top Line" and "Left Column".

Write the Search formula at the row/column intersection

On Excel (all versions), the intersection operator is a space.

Distance for Lille Marseille, simply enter the formula in B7
=lille marseille
or this formula
=marseille lille
Simply seperate the name of the line and that of the column with a space.

Use a formula with INDEX and MATCH functions

The previous technique is suitable for small tables.
So for large tables, use a formula that includes the INDEX function
The arguments of this function (the reference row and the column) will be provided by the MATCH function, which will refer to:
- The titles column for the concerned rows.
- The titles row for the concerned column.
With the title of the (lille) row in A1 2 and the one for the (lyon) column in B12, the formula is:


When creating names , you shouldn't start with a number, use a space or a special character as / \ # ! ? : * @.
In older versions of Excel, click "Insert / Name / Create ..." and check "top line" and "left column".

Thank you to Eric (pseudo eriiic) who proposed these methods via the Office forum - 14/10/2012:
For unlimited offline reading, you can download this article for free in PDF format:

See also

In the same category

Nommer des plages, trouver une intersection ligne-colonne
By Raymond PENTIER on November 2, 2012
Original article published by Raymond PENTIER. Translated by deri58. - Latest update by aquarelle
This document entitled « Excel - Define ranges and find the value at row-column intersection » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.