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! 😉
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
Hi Tom – I’ve been searching through the blog for your solution to the 2-columns + 1-row list question posed by lohhw3 in 2011, and I’m having trouble locating it. Any chance you could point me in the right direction?
Hi Cory – –
Might this be what you were looking for?
https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-intersecting-lookup-with-multiple-criteria/
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/
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.
What does the formula return if you change the 4th argument of FALSE to TRUE?
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!
Happy to help.
Here’s a one-minute video I did about that optional range_lookup argument that might help clarify things for you.
https://www.youtube.com/watch?v=MbXCRdcAo1M
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 -)
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.
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.
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”?
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?
Awesome stuff Tom -helped heaps
Thank you, Sean!
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.
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
This is a duplicate post. Please see my reply on your other post.
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
Can you give a larger sample set because I do not understand the logic of how your data is organized or where it is.
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
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.
Thanks. This was the easiest example I’ve seen
Thanks, Walter!
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!
Thank you!!
The Best explanation I could find. Thank you!
Thanks!!
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
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.
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))
Thanks for the follow-up, Sam!
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?
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.
Hi Tom yes I would appreciate the assist, I was not able to get it figured out.
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.
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?
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.
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!
Hello, Mike.
Can you offer some perspective or background info as to what data you are working with, and what your expected result is.
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.
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.
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
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.
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.
wherever the condition match (value >=5), the result must return (Column Name, Row Name)
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.
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.
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.
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.
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.
Cool — thanks for the update!
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?
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.
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.
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.
Thanks, I appreciate your great feedback.