Tom’s Tutorials for Excel: Look up Intersecting Value by Row and Column Criteria

Tom’s Tutorials for Excel: Lookup Intersecting Value by Row and Column Criteria.
Here’s another one of Tom Urtis’ nifty tips in a quick format. Enjoy & Excel!

Using two criteria (one as row header label and the other as column header label), an intersecting value is returned, as seen in the pictured table with this formula:

=INDEX($A1:$M10,MATCH(A16,$A1:$A10,0),MATCH(A19,$A$1:$M$1,0))

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
78 comments on “Tom’s Tutorials for Excel: Look up Intersecting Value by Row and Column Criteria
  1. Simon Catich says:

    Tom’s Tips for Excel concept impressed me so much. This program is very effective and very learning’s. So lot of thanks to you tom. I’m waiting for your next lesson 🙂

    • DanaH says:

      Thank you, Simon ! Glad to hear it’s useful to you. Check us out on Twitter @TomUrtis. I’ll be posting more of Tom’s Quick Tips there everyday & posting a weekend review of all Tips on Saturday & Sunday, right here.

  2. lohhw3 says:

    another awesome tutorial 😉

    suggestion: any chance of finding intersecting values for 2-columns + 1-row list?

    e.g.

    Column A=Operations
    Column B=Year

    Row 1 ($C$1:$N$1) = months from Jan till Dec

    to keep the tutorial short, each Operations will have 2 years (2011 & 2010)

    so the index/match search will find Operation vs Year vs Month.

    thanks!

  3. Finaz says:

    Not sure if i can explain it well, but i have a question.. can this formula be used to get info from one table and put it into another table?

    Example i have one table that has details breakdown:

    Component I Time Band I Amount
    Loans I 1 month I 150
    Loans I 1 month I 20
    Loans I 1 year I 100
    Borrowing I 1 year I 100

    Then i want to automatically sort the information into another table:

    I 1 month I 1 year
    Loans I x I y
    Borrowing I I z

    So basically x=170, y=100 & z=100…

    i know this looks like a pivot function, but the 2nd table is actually a report and the intention is that the report will be automatically updated when table 1 is updated…

    pls help me.. thanx

  4. Brandon F says:

    Thank you, Simon. Very clear, succinct example.

    I know this is a bit old, but taking it a step further…assuming you have data that identifies a column header (in your example, let’s say March) and you have a data point inside of the array from that column (let’s say 901), is there a way for you to be able to identify the associated row header (in this case, Postage)?

  5. William says:

    Good day
    I have table 1

    Jan Feb Mar Apr May Jun
    Ben 1 1
    Ben 1
    Ben 1
    Rick 1
    Rick 1
    Rick

    then I have Table 2

    Jan Feb Mar Apr May Jun
    Ben
    Rick

    I need a formula in table 2 that will look in table 1
    and bring back the number 1 for Ben under Jan , Feb , May and June
    and the same for Rick under Mar and Apr

    thanks

  6. Gaya Prasad says:

    Hi Dear,

    I have a excel data A column is unique value and other b to z column date, row 2 column b to z pending or done status date wise, i want to value wise done date.

    kindly help

    • Tom Urtis says:

      I understand that English is not your primary language but I want to help if I can understand better what you need.
      • In column A you have a unique list of data of some kind.
      • In each of the 25 columns after column A (that is, from and including column B to column Z) you have dates in those cells.

      What I do not understand is this you wrote:
      “I want to value wise done date”.

      If you can give an example of your data, and what your expected result is (and where that result should be, maybe column AA?), and why you expect that result (that is, the reasoning of your solution), then I can try to help give you an answer you can use.

  7. Neeraj says:

    Appreciate!!

    But what if I have a data, into which Computers comes twice, then

    Jan : 0
    Feb : 1

    Formula provided by yourselves will count 1st only i.e 0

    • Tom Urtis says:

      Your question does not seem to relate to this example. The formula returns the intersecting cell belonging to a unique column header criterion (in cell A19 in this case) and a unique row header criterion (in cell A16 in this case). In any calendar year there can only be one month of January and one month of February. In any operation there is only one department named Payroll, or Maintenance, and so on.

  8. Steve says:

    How do you add “if(isna” to this formula

  9. meera says:

    Table 1
    Hyd Mumbai Pune Rajsthan
    hyd1 MUM1 Pun1 Raj1
    Completed In Progress Completed Failed

    Table 2
    City Data 06-11-2016 07-11-2016 08-11-2016
    Hyd hyd1 completed
    Mumbai MUM1 In Progress
    Pune Pun1 Completed
    Rajsthan Raj1 Failed

  10. meera says:

    Hii I need data to be lookup from table 2 to table 1.Table 2 contains actual data. Hyd is city , hyd1 is data its value is present in date column 6/11/2016. now i need value completed in table 1 …All cities are in one row, data is in 2nd row. 3rd row should fetc fex fetch value from table 2. plz help….

  11. Max says:

    Bless you Tom. Was racking my brain and searching websites for over an hour, until i found your site with the perfect formula i was looking for. Thank you so much.

  12. Kim says:

    Hi

    I was wonder if you could help me please?
    I want to do sort of the opposite to this so I want to use the value in the cross reference to provide me with an output of the column heading and row heading. So u sing the data above for example,
    If I selected the value “901” I want to create a new table that will input:
    Postage March
    Postage June

    Or if I select “108” I want a new table to populate:
    Telephone Feb
    Taxes April

    (My data is a training schedule so it will flag up who needs what training each month)

    Thanks

  13. joe says:

    how do you reverse lookup. search the value and return the column and row label

  14. Andy says:

    Hi Tom, I wonder if you can help me?

    I am looking to achieve something similar to your example, only I want to use column headers that use a number range rather than exact names or numbers. To cover every possibility would require a very large and unnecessary table.

    Basically X axis headers will be prices, eg,
    10, 30, 50, 100, 200, 500
    Y axis headers will be months, eg.
    6, 12, 18, 24, 36, 48

    I then need to look up the price of my given item against how many months I have had it for. The resulting table entry will just be a number that I use to generate a best offer acceptance price. For example the price may be £80 and I have had it for 14 months.
    So when using the lookup and match functions I want to be able to return the value that resides (in this example) in the 50 Row and 12 column.

    The formula on my sheet to return an exact match to £50 and 12 months would be
    =VLOOKUP(AG4, Variables!$BB$14:$BH$20, MATCH(CD4, Variables!$BB$14:$BH$14, 0), FALSE)
    but as soon as the price or month changes outside an exact match, of course I get an N/A

    Do you know of any way it would be possible to gain the data I am seeking from my table, given any 2 values to cross reference, or would I need to create a table with all possible combinations?
    Or maybe there is just an different way to approach it?

    Sorry if I made that harder to understand than it needed to be, but I hope you may have made enough sense of it to help me out!

    Thank you.

  15. Andy says:

    Hi Tom, Thanks for your quick reply.
    Interesting. It appears that with TRUE, the formula holds good for changing months (these are on the Y axis and called by the cell AG4 in my example). The x axis (price/CD4) still produces an N/A when the values are not exact.

    However whilst writing this, I have followed on from your lead and tried changing the 0 in the formula to a 1, and now the prices are working correctly also!

    Really appreciate your help, would not have got there without you!

  16. Andy says:

    Hi Tom, cheers for the link, I’ve subscribed to your channel, lots of useful info there – your video on arrays was especially helpful to me. Thanks again for all your assistance -)

  17. Neil says:

    What would be to formula to find the maximum value of telephone and corresponding month, the formula should be able to give the answer even if the underline data changes.

    would want to know formula which especially also includes step to find telephone in the table.

    • Tom Urtis says:

      This example looks up a value based on row and column header labels. Your question does not fit this design because a maximum value would mean the lookup attempt is made on the value and not the header labels. So, if you can describe your worksheet in terms of (presumably) which column(s) contain what values, and an example of your expected results, we can whip up a formula to accomplish that.

  18. Mary says:

    In your sample you have data for each “Operation” and each “Month” in your table. What if “Month” of Aug and proceeding “Month” after that had no data for your Computers Operation and you want to pull the last non-blank value for that “Operation”?

    • Tom Urtis says:

      In that scenario, what does “last non-blank value for that Operation mean?
      • Last before August, meaning July if it has data, or June if July does not, and so on?
      • Or last meaning September or October or whichever month is the next month after August that has data, which means it might not literally be the last data after August for computers.
      • or literally the last data item in the Computers row.
      • and what if no data is in the Computers row? Or just in January or December when you search for August?

  19. Sean says:

    Awesome stuff Tom -helped heaps

  20. Juz says:

    Hi Tom,

    Interesting tutorial. In this example, what can be done I we want to print the row, column and cell value serially in 3 separate columns. For instance:

    Payroll Jan 8,459
    Payroll Feb 9,121
    .
    .
    .
    Maintenance Jan 4,201
    Maintenance Feb 8,584
    .
    .
    .

    Basically we need to print the row, column and cell value in the grid in to 3 separate columns and then be able to sort them.

    Thanks in advance for your help.

  21. SUDHA says:

    I have the some number and correcponding information in 2nd row. How to get a information Row in another sheet.

    Que:-
    no Information
    123 No data
    123 ABC
    124 DEF
    124 No data

    Result :-
    no Information
    123 ABC
    124 DEF

  22. SUDHA says:

    I have the some number 1st row and correcponding information in 2nd row. How to get a information Row in another sheet.

    Que:-
    Nos data
    123
    123 456
    124 867
    124

    Result :-
    Nos data
    123 456
    124 867

  23. Abid says:

    Hi Tom,

    I am facing some challenges need your help. kindly provide your email id so that I can provide my details there.

    Regards
    Abid

    • Tom Urtis says:

      My email address is on every page of my website. However, please ask your question here, so other people reading my blog and who have the same question can benefit from the solution.

  24. Walter says:

    Thanks. This was the easiest example I’ve seen

  25. Tomas Burian says:

    You sir, are the best! I needed to be able to look up a cost of a color paint for different car makes and this solved it for me! Thank you!

  26. PC says:

    The Best explanation I could find. Thank you!

  27. Sam says:

    Hey Tom,

    Need quick help with something.

    I have the following data in excel. Based on the row label, it has to go to the corresponding column label, and have to extract the intersection value of row and column. Can you please provide some guidance. I tried various functions, somehow no luck.

    Description ABC1 ABC2 ABC18 ABC58
    ABC2 0 1 0 0
    ABC18 0 1 0 1
    ABC58 0 0 0 1
    ABC1 0 1 0
    ABC18 0 0 1 0

    • Tom Urtis says:

      Maybe because of the disalignment but I don’t see how what you posted relates to what you wrote. Maybe if you show 2 tables, one being the original list and the other being the final result, I can follow along with what you need, which on the face of it sounds do-able.

      • Sam says:

        Thank you Tom. Was able to resolve myself with Index & Match function.

        A B C D E
        1 Description ABC1 ABC2 ABC18 ABC58
        2 ABC2 0 1 0 0
        3 ABC18 0 1 0 1
        4 ABC58 0 0 0 1
        5 ABC1 0 0 1 0
        6 ABC18 0 0 1 0
        7
        8 ABC2 1
        Sheet1

        Worksheet Formulas
        Cell Formula
        B8 =INDEX($B$2:$E$6,MATCH($A$8,$A$2:$A$6,0),MATCH($A$8,$B$1:$E$1,0))

  28. Robin Dhillon says:

    Hi, I am trying to do something similar but want to know how I could get multiple output? For example I have column names set to table names we have in our system and the rows are labeled as Category names. So I want to be able to enter a table name and a category name and return the codes that relate to both criterion.

    i.e.: TableName1 and CategoryName4 have 8 codes, how can the formula bring back the 8 codes instead of just the 1?

    • Tom Urtis says:

      Hello Robin – –

      Apologies for my delayed reply… I was traveling during the last half of September but I am back home in San Francisco and at my computers. If this is still an open item for you, please post back and I will be happy to assist.

      • Robin Dhillon says:

        Hi Tom yes I would appreciate the assist, I was not able to get it figured out.

        • Tom Urtis says:

          Re-reading your original comments from a couple months ago, when you say you want to return maybe several lookup items from 2 criteria, it almost sounds like what you might need is a pivot table. It’ll almost certainly be an array of some kind. In any case, try explaining a further example of your data layout, with an identification of what values exist in what cells in what spreadsheets or tables, and your expected results from a lookup action, and we can take it from there.

          Also, a tip and suggestion, I’m not always available because I have several client projects going at the same time. But there are a few very good Excel forums out there, one in particular at https://www.mrexcel.com/board/ which I contribute to when I get the chance, but there are always Excel experts who monitor the board and who would be able to help you faster than I probably could, because there are so many of them who can help. The service is free and easy to set up a personal account.

  29. Marcia Reinhardt says:

    Is there a way to have Excel look up information throughout a page. Here is the example

    Recipe Item
    Cookies Cinnamon Eggs Milk
    Pie Crust Eggs Apples

    Recipe Instructions
    Cookies Cook time stir oven temp
    Pie blend chop stir oven temp

    I need to find a way to look up multiple items in the array, but the kicker is that the sheet can grow and/or shrink based upon what is being made that day so I cannot just put a particualar row to look at for the header and in the column there will be multiple instances of the header as well. Any ideas?

    • Tom Urtis says:

      Hello Marcia – –

      Well, I can confidently say that your culinary skills are worlds ahead of anything I can even attempt to conjure up in the kitchen.

      To answer your question, I can, in chef terms, “whip up” a suggestion or two, but first I’d need more info:
      • Do you want only a formula solution or is VBA OK.
      • What does “look up multiple items in the array” mean. For example, are these multiple lookup items in a range of cells or is they VBA variables in memory.
      • How do you want the results to look. For example, do you want a list of each lookup value’s cell address or whatever is in the cell next to it, or their header value, or if they are numbers to be added up?

      The dynamic variations in your sheet’s used range would be no problem as the ranges shrink or expand.

  30. Mike Sato says:

    Hi Tom,

    Trying to use this formula across two tabs, I came out with this formula, but it is not returning the correct information from the table it is pulling from:

    =INDEX(IngredientCosts!$A$3:Sheet1!$I$9999,MATCH(Sheet2!$A4,Sheet1!$A$4:Sheet1!$A$99999,0),MATCH(Sheet2!$C4,Sheet1!$A$3:Sheet1!$I$3,0))

    Any help cleaning this up would be appreciated!

  31. Raúl says:

    Hi Tom,
    I have a question, what if I have 3 columns next to each other with their top cells combined in one, how could I reach the cell, say 5 rows down in the central column? The value on the top combined cell is a “date”. I have tried all vlookup, hlookup, match and index, but I cannot make them work together.

    • Tom Urtis says:

      Hello, Raúl:

      By “top cells combined into one”, I am assuming, but you need to clarify, if that means you merged those 3 cells for the date to be in. If so, formatting to center across columns is a better method than merging cells in 99% of the time, just a tip if you did merge cells.

      But regarding your question, I cannot tell from your description if you want to do a lookup or because you say that you know you want to find a cell 5 rows down in the central column, it could be an OFFSET(5, 1) reference meaning 5 rows down and 1 column to the right. This again assumes you merged the 3 top cells and the date value really (as far as Excel is concerned) is in the first (leftmost) column cell, which is why the 1 in the OFFSET function looks to the right by one column which is the center column as you said.

      Other than that, more details would be needed to understand your exact layout, such as range addresses, what data is in the 3 columns, and why you are looking up a date in the first place.

  32. Murtuza Ali Syed says:

    Hello Tom,

    I’m looking forward to getting something similar but the other way round. where, in a table, if the cell value is <=5 it must return the (column header name, row header name):

    Column 1 Column 2 Column 3 Column 4 Column Formula1-N
    Row 1 4 3456 4527 34576 (Column 1,Row 1)
    Row 2 234661 1 6324 562 #N/A
    Row 3 235761 62345 2346 34546 #N/A
    Row 4 5 2345 2765 24356 (Column 1,Row 4)
    Row 5 13755 2345 3672 236 #N/A
    Row 6 27517 72547 2724 234 #N/A
    Row 7 234 0 7243 7652 #N/A
    Row 8 323456 6132467 576 23761 #N/A
    Row 9 0 2345 234 8798 (Column 1,Row 9)
    Row 10 23452 34568 6 1 #N/A

    I will have to create as many as formula columns as the data columns I have.
    Kindly help me with a solution.

    Regards
    Ali

    • Tom Urtis says:

      Just to be clear, please clarify if you want a list of all the times when in each row a number less than 5 is found so you want that column and row header. In your example, every time that happens the column header is presumably B2, and the row header would be simple because it is the cell in column A of the row where that number less than 5 is found.

      I guess I don’t understand how you want the final result to look like or to be.

      • Murtuza Ali Syed says:

        Yes, I want a list of all the times when in each row a number less than 5 is found it shall return column header and row header.

      • Murtuza Ali Syed says:

        wherever the condition match (value >=5), the result must return (Column Name, Row Name)

        • Tom Urtis says:

          You are saying confusing things and not answering my question.

          In your last reply you wrote this:
          wherever the condition match (value >= 5)
          which means value greater than or equal to 5.

          In your first message you wrote this:
          “Yes, I want a list of all the times when in each row a number less than 5”

          So which is it: less than 5 or greater than 5?

          Also, I asked how you want the final result to be, such as in a list or all in one cell or something else.

          What I do not understand, which you did not answer, is why this is a problem in the first place. Your example shows only Column 1 with a low number so that makes it easy for the column which is always the same, and the row because that is the row of the number.

  33. Taygon-Lee Claassens says:

    Hi Tom.

    Great formula. I have one issue though. The formula gives me the value of either the 1st row in the series or the 1st column. I have a rate matrix and basically want to create a calculator where a origin and destination is selected and a rate is given. Formula as follows: =INDEX($A100:$G106,MATCH(B3,$A101:$A106,0),MATCH(C3,$B100:$G100,)). B3 and C3 are Data Validation fields that you can select from a dropdown list.

    • Tom Urtis says:

      Thanks and yep; the reason for the first row or first column is that the example assumed unique header labels. Your project sounds more complex; any time I see or hear the word matrix it’s bound to be interesting.

      • Taygon-Lee Claassens says:

        How will I overcome this? The row and column headers do have repeating values. The only other alternative is if I split my matrix and make it a linear table with values on top of one another but I wouldn’t want to go this way.

      • Taygon-Lee Claassens says:

        I managed to overcome my issue with a different formula. =VLOOKUP($B3,$A100:$G106,MATCH($C3,$B100:$G100,0)+1,FALSE). Thanks again as your formula helped me to make my own.

  34. Robert R. says:

    Using your formula, I can find the value of a row/column intersection. That’s great but, how can I change the existing value of the intersection cell without going into the table?

    • Tom Urtis says:

      What do you mean, “without going into the table”?
      You can enter the formula anywhere outside the table, where you would be entering the formula anyway.

      • Robert R says:

        Say I have a table with a list of properties in column A and there are 100 columns with all sorts of headings on page 51.
        Now, I’m on page one and make a search which gives me a value in column 36 row 78.
        While staying on page one, I want to make a change to the value which I found in column 36 row 78.
        So to summarize, find a value in a cell and change it.

        • Tom Urtis says:

          You would need VBA to accomplish that, but it is do-able if I understand you correctly. The code would look at your search function, or if it were me I would build the search as part of the code, but in any case when you know the worksheet and cell where the data needs to be changed, it just becomes a matter of accepting, either in a worksheet cell on “page one” or in an Input Box, what the edited value is, and programmatically put that value into the identified cell. That’s the theory answer; the practice answer depends on your preference for how the activity looks from the user’s standpoint, and of course if VBA is approved in case this is a workplace project where IT has limited or made unavailable any programmed projects in Excel.

Leave a Reply

Your email address will not be published. Required fields are marked *

*