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

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

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.

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!

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/

wow! thanks Tom for the excellent solution! π

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

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

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

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

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.

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

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.

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

Can you explain why you want to do this, and what should happen if isna = true.

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

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….

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.

Thanks, Max!

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

See my blog post about that here:

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-reverse-intersect-lookup-for-headers/

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

I posted that solution on this blog entry:

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-reverse-intersect-lookup-for-headers/