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


Share Button

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))

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
22 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/

  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

Leave a Reply

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

*