Software

Create an easy two-way lookup in Excel

Use this easy-to-remember and easy-to-implement solution for intersecting rows and columns.

Spreadsheets store data in a matrix-style format. You find specific data by pinpointing the intersection of two details. For instance, the spreadsheet below is a matrix of personnel and regions. If you want to determine the number of units Michaels sold in the West (39), you can run your finger down the Michaels column or across the West row until it intersects with the other point. Now, you probably know all that already. I'm absolutely sure that you know that using your finger to find data isn't efficient or reliable!

There are a couple of complex solutions for satisfying this type of dynamic two-way lookup, but they're too complex for me to remember. I always have to look them up to get the syntax just right. There's nothing wrong with that, but there's an easier solution—one that I (and you) can remember.

First, you need to add two validation lists to the sheet so users can specify both row and column values. To create a list of regions in cell B1, do the following:

  1. Select cell B1.
  2. Choose Validation from the Data menu to display the Data Validation dialog box. In Excel 2007 and 2010, choose Data Validation from the Data Validation dropdown in the Data Tools group on the Data tab. Or, press [Alt]+D+L.
  3. Choose List from the Allow dropdown.
  4. Enter =$A$6:$A$9 in the Source control.
  5. Click OK.

Next, create a validation list in cell B2 for the sales personnel listed in row 5, as follows:

  1. Select cell B2.
  2. Choose Validation from the Data menu. In Excel 2007 and 2010, choose Data Validation from the Data Validation dropdown in the Data Tools group on the Data tab. Or, press [Alt]+D+L.
  3. Choose List from the Allow dropdown.
  4. Enter =$B$5:$E$5 in the Source control.
  5. Click OK. At this point, you have two validations lists so users can select both a region and a name.

You still need a formula that can find the intersecting cells between the region and personnel. But first, the simple formula you'll be using relies on range names—a range name for each name and each region. Now, that sounds pretty complex, but in truth, it's easier than you might think. To quickly define a name for each region and name, do the following:

  1. Select the spreadsheet. In this case, that's A5:E9 (don't select the row of totals).
  2. From the Insert menu, choose Name and then select Create from the resulting submenu. In Excel 2007 and 2010, choose Create From Selection in the Define Names group on the Formulas tab. Or, press [Ctrl]+[Shift]+F3.
  3. The default settings in the Create Name dialog box, Top Row and Left Column, are what you want, so click OK without changing anything. (If that's not what you get, try again—did you select both the row and column headers?)

With just a few clicks, you created eight ranges: North, South, East, West, Smith, Jones, Michaels, and Hancock. Now, you're ready to enter the simple formula that pulls all the pieces together. In cell B3, enter the following formula:

=INDIRECT(B1) INDIRECT(B2)

Note, there's a space between the two functions—that's the Intersection operator. This operator returns the value in the cell(s) that's common to the references.  In this case, that means the formula will return the value that's common to the references in cells B1 and B2—the region and personnel names, which happen to represent ranges! (The formula will return an error at first, but the operator isn't the problem--it just needs values to evaluate).

Now, with everything in place, choose a region and a name from the two validation lists in B1 and B2, respectively. Below, you can see the result of choosing South and Hancock (55).

This formula evaluates as follows:

=INDIRECT(B1) INDIRECT(B2) =

=INDIRECT(South) INDIRECT(Hancock) =

55

Some of you might think this solution is convoluted because of all the steps, but for me, this solution is much simpler than a complex lookup formula. I can remember the pieces, but I can't remember a complex formula that relies on several functions and very precise syntax. You'll seldom (almost never) hear me say that my way is the best way to do anything but I prefer this solution because it's easy to remember and so, for me, easy to implement.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

40 comments
aksalaymeh
aksalaymeh

very nice as you said I don't have to remember the syntax of functions and make mistakes

hok.yap
hok.yap

Refinement with tables instead an absolute range definition The defining of names for the columns and the maintenance of the lookup region can be given to excel so you have reduced maintenance and a higher readability. The index function is an efficient function. The indirect function is slow. If you are working on large spreadsheets the index/match is more efficient Vlookup is an old way to do the lookup and forces a first column structure. The following formula has not to be changed if the Region is placed in the middle of the table or if multiple lookupcriteria are available. - Define name B1 INP_region - Define name B2 inp_personel - Define header A5 Region - Insert table Turnover - the index formula without absolute cell references is now =INDEX(Turnover,MATCH(inp_Region,Turnover[Region],0),MATCH(inp_Personel,Turnover[#Headers],0))

mhast
mhast

hi,lookup is most formula in Excel. its a short way for Excel. Compare ISAs mike....

Prendo
Prendo

I'd prefer to include totals in the lookup. You can achieve this by adding a total for the Regions in Column F, then summing across. This summation should be included for the Totals in row 10. Put a heading on the column of RegionTotal (no spaces as per previous!). Next change the Total label in A10 to RepTotal. Then simply extend the range of the validations in B1 and B2 to include the total cells. Now you can also lookup the Region Total for a region, the Personnel Total for a Rep, or the Grand Total (by selecting the respective total for both input cells). jep

vinodmerch
vinodmerch

This indeed is a good option. I worked with the data given here and works well but if I try with some other data it gives me either #REF pr #Null error. Is there any limit to number of cells in the selected area? Is there a way by which I can attach my Excel workbook and send it to you? Vinod Merchant

LocoLobo
LocoLobo

I like it. For me the INDEX, VLOOKUP, and HLOOKUP functions are easier to understand. I never understood what the INDIRECT function was supposed to do.

danny
danny

Hi Susan - Thank you for reminding us about how to use the "SPACE Operator" when doing lookups. I simply create named ranges and then use SPACE to find the intersection. Here is a link to a short video that I created demonstrating how I use the SPACE OPERATOR to lookup intersecting values in a structured dataset. http://www.thecompanyrocks.com/simplify-data-lookups-in-excel/ Sincerely, Danny Rocks The Company Rocks

GazSkeltz
GazSkeltz

Great Tip, but make sure there aren't any spaces in your row or column headings - the named ranges that are created replace spaces with underscores making the indirect unusable. In this case, use the following for the indirect functions: =INDIRECT(SUBSTITUTE(B1," ","_")) INDIRECT(SUBSTITUTE(B2," ","_"))

jbenton
jbenton

I'd have used offset(a5,match(b1,a6:a9,),match(b2,b5:e5,)) - but I like the simplicity of Susan's solution NB I always write formulae and cell refs in lower case because they change to upper on changing cell, making many basic errors easy to spot

argeedblu
argeedblu

Good idea and article. However, I have a couple of picky suggestions. I have re-read the article several times but did not find any mention of inserting four rows above the table. You would also eliminate some abiguity if you said "Select the table..." instead of "Select the spreadsheet..." You also might want to incorporate an error test in cell b3 so that it displays blank when one or both of the lookup values is empty.

andre
andre

=VLOOKUP(B1,A4:E9,MATCH(B2,A4:E4,FALSE),FALSE) would give you the same results

kathy76999
kathy76999

I like it! Thanks for sharing! I'll check out the index function suggested by the other commenters too, but this is a very easy way to do something incredibly useful. Thanks!

Mark W. Kaelin
Mark W. Kaelin

This is a pretty clever idea, but have you got a better one? Don't keep it to yourself, tell us about it.

steve.lehnhard
steve.lehnhard

Thanks for that video, very clear, easy to understand and a great tip to boot!

jv1357
jv1357

Now this is extremely easy and useful solution. Even better than the solution proposed in the original article. Thanks, Jarda

lkuhlman
lkuhlman

All I can say is wow! I've been doing it the hard way. I have bookmarked this one for future reference. THANKS DANNY!

Arlene
Arlene

Thanks for sharing this video! I ran into a problem when the range names are part of a pivot table. I couldn't select the range. Any ideas?

Varseller
Varseller

An excellent video and very clearly presented. Thanks.

sgraye
sgraye

I agree. Great video. S.

jdreyer751
jdreyer751

Super easy and very clear presentation - I now know about the intersection operator (space) and the name ranges command. Super!!

ssharkins
ssharkins

Thanks for mentioning that -- I meant to and ... just forgot!

paulg9
paulg9

Yeah, I, too, wondered where those rows above the table came from. Reading along while looking at the first Figure, I was wondering why on earth they were referencing the empty cells at A6:A9. Nice point about using an error test in cell B3.

sgraye
sgraye

Just to confirm. I use this solution all the time. S

seabruce
seabruce

For some reason, people seem to want to use Excel to do what they should be doing in Access (or other database) because initial set up is easy, but when you want to do database operations, the work arounds are complicated.

billymak10
billymak10

I guess the complex way to do it would be the index() fn. which I personally use almost daily. The index function basically do the same thing in just one cell. =index(x,y,z) x is the array where the table is. (see the first pic in the post) x = $A$1:$E$5 y is the row # I would just the function match() to get the row # y = match("South",$A$1:$A$5,0) z is the column # z = match("Hancock", $A$1:$E$1,0) If you put all that together =index(x,y,z) It should get you 55

danny
danny

Hi Steve, I am pleased that you enjoyed my video tip for Excel. I try to post at least one new video lesson each week on my website. Danny Rocks The Company Rocks www.thecompanyrocks.com

danny
danny

Hi Arlene - Remember that a Pivot Table allows you to move (Pivot) the data fields - so it is not possible to "name" cells in the Pivot Table. Simply create names for the cells and ranges of cells in your source data. I hope that this helps. Danny Rocks The Company Rocks

danny
danny

Thank you for your kind comments about my Excel video. I work hard to make the explanation as simple as possible. And, since Susan started the topic of "Simplicity" this is a good concept to remember. Danny Rocks The Company Rocks www.thecompanyrocks.com

Varseller
Varseller

sometimes you're in a situation where you have to make the information available to other users, managers or possibly execs to use in their decisionmaking or review process. Most of those people aren't familiar with Access or other database software but almost all use Excel. They want a point and shoot solution. "Umm, let me see how John is doing in Northwest". This is a perfect solution and can make you look like a star.

and2802
and2802

When I tried using the exact functions shown, I had to adjust the Y and Z because I used a table that didn't start in A1. Same is true when using VLOOKUP(). Is it assumed the user will know that, or am I missing something?

jpl1953es
jpl1953es

This trickm using index() looks nice, but it means that every time someone requires a data, he must write the formula, hardcoding data, with the error flying arounm him. However, the original trick has the advantage to use valiation lists, that user just need to SELECT DATA. I've used indirect() in other allications, mainly in graphs and worked without problems. I'll try ti automate the index() to see if it works

ssharkins
ssharkins

The INDEX() one-formula solution is what I referred to early in the post -- the one I have to look up to use. For me, by the time I look it up and rewrite the references, I could've implemented the INDIRECT() solution that I wrote about. That's just me though -- I'm glad to hear how others solve this problem.

Varseller
Varseller

But you have not addressed the point that, unfortunately, the larger population as of right now simply doesn't know how to use or is intimidated by Access. This is particularly true of those higher in the organization. That's why we give them the results in Excel format (it's still the same "data" that you and I and others have produced from Access). I went back and found your comment that agrees with this point: "in the post above, the "well, everybody has Excel - need a point and shoot app" - there is nothing preventing you from converting ANY data back to Excel when you are through parsing, calculating, querying, appending, blending, etc. - Excel can still be your "common denominator" to SHARE the data - but do not make the mistake of feeling that ALL WORK has to be done IN Excel, just because that is the app you present the results in!" Also, you said this: "The only thing I ever use Excel for is a) when I need to produce a long list of sequential numbers quickly b) when I need to do mathematical calculations with data (rarely, and, Access can do this, just not as quickly) c) to present work I did in Access in a format that "everyone" can use But...everyone COULD use Access, if they would at least learn the basics." I'm puzzled because we are saying exactly the same things. Shoot, I've taught so many people how to use Access instead of Excel when I saw what they were trying to do it isn't funny. The younger ones catch on quickly; the more "entrenched" ones (for want of a better term) resist fiercely. I'm sure you've seen that. Fortunately, most colleges teach Access as well as Excel nowadays, so the tide is slowly turning but it isn't there yet. These people will know which tool to use for the particular need. I've enjoyed this discussion but now I realize that we are both saying exactly the same things -- maybe semantics got in our way. BTW, re: "What happens when the results of the analysis you asked me to do are equal to 67,000 rows?" Well, anyone looking at that much detail isn't likely to be in middle or upper management (the subjects of my original post). The person looking at that much data had better learn some Access basics FAST because they're still going to be looking for specific information within that data, and Excel it not the way to go. Quote from: kicker on May 24, 2006 "...and Toppa didn't really have it wrong, she was just saying the same thing in a slightly different way. yeah..we know she didn't have it wrong...but she thought she was saying something different..in which case..she was wrong ;D No, I thought that Trinidadian was implying something contrary to what the article said. That's where the mix-up was." HaHa on us! Peace.

staffordd
staffordd

"You and I don't disagree on the tools; my point was simply about presentation based on the final user. No offense intended, but if that makes NO SENSE to you, I'm sorry I can't make it more clear". If we don't disagree on the tool, then there is no problem. I am pretty sure in one of my two posts I said exactly the same thing, that I would do the manipulation in Access...and then port it into Excel so "everyone" can understand. I still think that a lot of people waste a lot of time doing a lot of really, really complex and tricky formulas in Excel, because they are either afraid of Access, won't take the time to at least learn the basics, or just can't be bothered. That frustrates me, because for me, once you understand the awesome power of something like the QBE grid, the expression builder, or in particular, the Update Query - if you took the time to learn just those three things, you would probably NEVER work in Excel again. The only thing I ever use Excel for is a) when I need to produce a long list of sequential numbers quickly b) when I need to do mathematical calculations with data (rarely, and, Access can do this, just not as quickly) c) to present work I did in Access in a format that "everyone" can use But...everyone COULD use Access, if they would at least learn the basics. What happens when the results of the analysis you asked me to do are equal to 67,000 rows ? Unless you have the very latest version of Excel, you can only work with 65,000 rows - so I can't really help you. I could send you an Access database, but, it isn't "for everyone" so I guess I have wasted my time. Oh, I know. I'll put the first 65,000 rows on ONE TAB, and the other 2000 on ANOTHER TAB ! That'll make it work... You see how ridiculous it can all get. Yes, I remember dBase, and Lotus 1-2-3, and wp51, and God only knows what else, you are not the only older person here... Sorry I offended you, I thought we agreed, but it's not important, really, it's not. I hope the "Excel only" users, especially those making VERY TRICKY formulas, lookups, ranges and so on, will take heed to the things that these old Access codgers are saying. Just the Update Query alone, will just blow your mind. Imagine, an empty work space. You grab one table, and throw it in. You grab another table, and throw that in. Say one is "Sales", the other, "Cities". Each contains different data, but they have a common key, a matching field. You draw a line from one table to the other. which creates a join on the matching field. So for sale ID number 1734, it now can match that sale ID between the two tables. You then grab any field, say, the "City" field from the "Cities" table. Pull it down to your query by example grid. Then grab some fields from "Sales", say, "Gross", "Net", "Year". Under Cities, type in the Criteria "Milwaukee". Under Gross, type in >10,000. Let's say in the Sales table, there is a blank field called bonus. You grab that field. Then, run the query as a select query. You see all the records for Milwaukee ONLY; where Gross Sales are > 10,000 and a blank bonus field. You are feeling generous. You want to give your Milwaukee sales team a 17% bonus for every gross sale they made that is over 10,000. You change the design of the query to an Update query, and type an expression, using the expression builder, in the Update Window, telling it to take the value in the Gross Sale column, calculate what 17 percent of that amount is, and update the contents of the EMPTY bonus column with that amount. Once you have written the expression, you run the update query. It updates EVERY row (even if it's say, 750,000 rows) in a matter of seconds. In fact, this entire query that I have just described, I could create it and run it, and have the bonus populated, in probably less then THIRTY SECONDS. It's taken me FIVE MINUTES to type this ! But the update query - it can update any field, existing or blank, with any value, any calculated value, you could null the field out, you could change it to an altered version of another field - endless transformations, and, in SECONDS. Not minutes. No typing formulas, no naming ranges, no incredibly slow vertical or horizontal lookups - just, push the button, and three quarter of a million 17 percent bonuses for every Gross Sale larger than 10,000 dollars, in the city of Milwaukee ONLY, has been populated INSTANTLY. You have calculated, and updated, based on complex criteria, three quarters of a million rows of data, in LESS THAN ONE MINUTE. If you are interested in power like this, try learning Access, instead of mocking it, or trying to make out that Excel can do anything Access can - I've heard that all my life, but the truth is, it cannot compete with the SHEER POWER of a massive Update Query. It cannot DO IT. I hope my point is clearer now... have fun ! d.

Varseller
Varseller

I agree with you 100% that Access is the far better tool for data manipulation and querying. That was not my point at all. And let's be clear, I'm an advanced Access user and have over 25 years using databases (do you remember Paradox and dBase? -- I grew up on those babies as well as Lotus 123 for spreadsheets before Excel). I do almost all my complex manipulations/queries in Access and convert the results to Excel for general consumption. Having also been an executive I can tell you unequivocally that most simply want the presentation in a format they are familiar with. Hence, as a consultant I give them the parsed data in an Excel spreadsheet with the tools (such as this one) to "point and shoot". It's a happy compromise. They don't care (or know) about the Access manipulations I've used behind the scenes to parse the data they want. You and I don't disagree on the tools; my point was simply about presentation based on the final user. No offense intended, but if that makes NO SENSE to you, I'm sorry I can't make it more clear.

staffordd
staffordd

>>>sometimes you're in a situation where you have to make the information available to other users, managers or possibly execs to use in their decisionmaking or review process. Most of those people aren't familiar with Access or other database software but almost all use Excel. They want a point and shoot solution. "Umm, let me see how John is doing in Northwest". This is a perfect solution and can make you look like a star. the previous poster, the gentleman who said you should be using a database, is actually correct (please see my new post entitled "hmmm"). in the post above, the "well, everybody has Excel - need a point and shoot app" - there is nothing preventing you from converting ANY data back to Excel when you are through parsing, calculating, querying, appending, blending, etc. - Excel can still be your "common denominator" to SHARE the data - but do not make the mistake of feeling that ALL WORK has to be done IN Excel, just because that is the app you present the results in ! I spent over TEN YEARS working 90 percent of the time in Access, and then, when done, EXPORTING MY RESULTS to Excel for people to look at. Most of them never realised that all the work was done OUTSIDE of Excel, and Excel was used almost not at all. Susan - your reply that if it's Excel data you will use Excel to work it seems like a cop out to me. To my mind, data is just DATA. There is no such thing as "Excel data" or "Access data" or "text data". There is DATA, in various formats, with delimiters, without, etc. You have something that's about calculating that you need to do with data? Excel is your choice. You have something that's about complex multi-table or multi-field querying that you need to do with data? Access is your choice. You have something that's about presenting results to others?? You can use Access or Excel, text files, csv files, or a combination of all four to create your data, then export it to Excel so it's in the "common format". If you feel you must use Excel because you are inexperienced with Access, or you feel you "have to" - you are painting yourself into a corner. I really feel that what some of us are saying here is being brushed aside, but the truth is - this problem is easily solved in the world of database queries, and is overly complex, requiring far too many steps and far too much TIME - so, the wrong tool is being used. Sorry, but as a user of BOTH Access and Excel, I strongly believe that what I am saying, and what others here are saying and have said in other threads, is really true. Try it, you'll like it. But really, Ms. Harkins, Susan - there is no such thing as "Excel data" ! There is data that is MORE SUITED to Excel, and there is data that is MORE SUITED to Access, and in some cases, I might use BOTH to get the results I need. Sticking with just one, and creating these utterly long, convoluted impossible to follow routines, when a query that you could create in about 13 seconds would return exactly what you want...I see no point to behaving this way. No offence intended, but it just makes NO SENSE to me ! with love dave

ssharkins
ssharkins

If data's in Excel, I'm going to use Excel tools.

jpl1953es
jpl1953es

Solution: Don't type the ranges. Instead, select in the spreadsheet, lloking at the example. It works

staffordd
staffordd

Well, whenever I read something like this, I just feel that I am fortunate that I have spent very little time working in Excel, and thousands of hours working in Access. I am sure that Susan's method is good and valid and clever, experience tells me that Susan knows her stuff, and for Excel users, I am sure this is very helpful. But, and this is just for me, just me, my very first thought is "why aren't you doing this in Access?" Because in Access, with the same data in the same table, you can just...write a query that asks for what you want, and it will return it. No formulas, no calculations (save any you include in the query design, but even so, still far less tedious than Excel) and certainly no need to define 8 ranges or 8 ANYTHING. I think that people try to force Excel to do all kinds of things that while it CAN do them, it's really not ideally suited to do. Just as I would NOT use Access to add up a long string of numbers, I tend to not use Excel for complex querying. But - as Susan so rightfully said "that's just me". So my response to ALL of these ideas, suggestions, variations is - when you reach a certain level of complexity, where you need to query multiple elements either in two tables or in a complex way within one table - why not use a database tool with the power of the QBE grid, the expression builder, and end up taking far less time and far fewer steps then you will doing it all these "tricky ways" in Excel. Or so it seems to me..... peace and love carry on to each his/her own... dave

Editor's Picks