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: , , , , , , , , , , , ,
39 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!

    • TomU says:

      Hi lohhw3 – –

      Thanks for your comment and for an interesting question about having two columns instead of just one, in addition to the row labels, for looking up intersecting values. Check out the blog page where a solution has just been posted with an example in response to what I think you were asking for. – Tom
      http://www.atlaspm.com/blog/

      • lohhw3 says:

        wow! thanks Tom for the excellent solution! 😉

      • CecilA says:

        Using the same table above, let’s say I want the total of Postage, Utilities and phone for the month of August. Using a defined name, Administrative Expenses. What should the formula look like. I want to use the same formula for a different months.
        Desired result in a different worksheet.

        Mar Jun Sep
        Administrative Expense 1,928

  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

Leave a Reply

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

*