A two-way lookup is one of those everyday data tasks where Excel gives you plenty of options, but none of them feel totally satisfying. It is hard to avoid some messy nesting, usually involving a mix of INDEX(), MATCH(), lookup functions, and more.
Python in Excel offers what I think is a more intuitive approach, thanks to the central role of indexing in Pandas. Let’s take a look using a small dataset below. Download the file to follow along.
This dataset is five classic films, one per row, with four columns: title, year, reviews, and rating. The first column is different from the rest. Movie holds the labels that identify each row, while the others hold values you’d want to pull out. That makes it the index, the thing you look rows up by.
A two-way lookup grabs one value by naming two coordinates: a row and a column. Ask for “Casablanca” and “Reviews” and you get 122. This is everywhere in business because most reference tables look just like this: an ID column (customer, SKU, region) followed by attribute columns. It’s how you answer “what’s the price for this product?” without scrolling or filtering by hand.
Let’s see how Python in Excel provides a cleaner alternative to two-way lookups by using a proper index column.
Step 1: Bring the Excel table into Python
Let’s start by bringing the Excel table into Python:
movies_df = xl("movies[#All]", headers=True)The result is a pandas DataFrame, which you can think of as a Python version of an Excel table. It has rows, columns, headers, and values, but it also comes with a powerful indexing system.
Step 2: Set the lookup field as the index
Next, we set the Movie column as the index:
movies_df = movies_df.set_index("Movie")
This step is important. In Excel terms, we are telling pandas, “Use the movie names as the row labels.” Instead of referring to rows by their position, such as row 2 or row 7, we can now refer to them by movie title. Notice how the index column is visually set apart from the rest of the DataFrame. It is almost like the movie names are nested outside the main body of the data.
Step 3: Look up one row and one column
For example, to return the number of reviews for Casablanca, we can write:
movies_df.loc["Casablanca", "Reviews"]
This reads almost exactly like the lookup we want:
“Go to the row labeled Casablanca, then return the value from the Reviews column.”
That is the heart of the two-way lookup. The first item inside .loc[] identifies the row. The second item identifies the column.
In Excel, this might require matching the movie name down the first column and matching the field name across the header row. In pandas, once the DataFrame is indexed properly, you can point directly to the row and column by name.
Step 4: Return multiple columns
We can also return more than one column for the same movie:
movies_df.loc["Casablanca", ["Year", "Rating"]]
This returns the Year and Rating columns for Casablanca.
Notice the double structure here. The row is still a single label, "Casablanca", but the columns are provided as a list: ["Year", "Rating"].
Step 5: Return multiple rows and columns
We can go one step further and return multiple rows and multiple columns:
movies_df.loc[["Casablanca", "The Kid"], ["Year", "Rating"]]
Now pandas returns the year and rating for both movies.
This is where the pandas approach starts to feel especially clean. Instead of rewriting or nesting a more complex formula, we can expand the lookup by passing lists of row labels and column labels.
The pattern to remember
The basic pattern is:
dataframe.loc[row_selection, column_selection]That row selection can be one movie or a list of movies. The column selection can be one field or a list of fields.
Learn Python in Excel live
Interested in going deeper with Python in Excel? Join my upcoming live training, Python for Excel in a Week. The course runs Monday through Friday, with five live online sessions plus recordings included.
We will cover how Python fits into Excel, how to work with pandas DataFrames, how to clean and reshape data, build visualizations, create forecasts and models, and work with text data inside your workbook.
The course is designed for Excel users, analysts, accountants, finance professionals, and operations teams who want to bring real Python analysis into Excel without leaving the spreadsheet environment. No prior Python experience is required.