Tom’s Tutorials For Excel: Intersecting Lookup With Multiple Criteria
I received an interesting comment and request from a vistor named lohhw3, who asked a follow-up question about this tutorial for intersecting lookups. The question was how to look up a value in a table when there are two columns of criteria (Operations and Year in this example) in addition to the row header criteria for Month.
In the picture, two columns need to be considered in order to determine the correct row. With the criteria specified in range A24:C24, the formula in cell A27 that returns the correct value is
=INDEX(A1:N19,MATCH(1,(A1:A19=A24)*(B1:B19=B24),0),MATCH(C24,$A$1:$N$1,0))
Note that this is an array formula, meaning you must commit it to the cell by pressing Ctrl+Shift+Enter, not just with Enter.
Thanks to lohhw3 for the excellent question!
Hi there,
What happens when we have 2 column headers? is it the same procedure? Thanks
sorry, it’s 2 row headers 🙂
See if this helps:
https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-intersecting-lookup-with-multiple-criteria/
Hello Tom,
Using the same table you have, I want the sum of office rent and utilities and I created and defined name for this as admin expense. so as oppose to using =A24, I can use a defined name. how would the formula look like. I still have the same criteria for the column.
{=sum(sumif(sheet1$A$A,_adminexpense,sheet1!$G:$G))} . Here, I want to change the $g:$G to match the criteria, to July (which would be from a different column.
AdminExpense is a group of different operating expense.
Desired result:
May July October
Admin Expense
Maintenance &Landscaping
Would/Could this be any combination of months? Maybe 1 month or 2 months or 5 months, each time being different? How would you want to set the criteria (that is, have the formula know) for which month(s) you want to return a result for?
The months would have its own column. basically I am creating another report that will show
admin Expense (comprised of office rent and utilities) For any month that I choose. so if I choose May, it will give me the result for the month of May, so if I change the criteria which will be in a different column such as July, it will give me the admin expense for July.
I have a separate list of operating expense and defined name for the expenses that fall under admin expense. I would like to use the defined name (the result will be the sum of what was defined in that name) in the row criteria.
CAN YOU PLEASE HELP ME WITH THIS…
STOCKNO. DEL DATE QTY
P013-8737A 10/11/2016 2
P013-8737A 10/11/2016 2
P013-8737A 10/11/2016 2
P013-8737A 10/11/2016 1
P013-8737A 10/14/2016 2
P013-8737A 10/12/2016 3
P013-8910P 9/27/2016 2
P013-8910P 11/12/2016 2
P013-8910P 9/8/2016 1
P013-8910P 9/27/2016 1
P013-8910P 9/27/2016 1
P013-8910P 9/27/2016 1
P013-8910P 9/27/2016 1
P013-8910P 9/27/2016 1
P013-8910P 10/14/2016 1
P013-8910P 10/24/2016 1
P013-8910P 10/28/2016 1
I JUST WANT TO GET THE DELIVERY DATE WITH THE HIGHEST QTY OF EACH STOCK NUMBER.
THANKS:)
The first part of the task would be to create a list of unique stock numbers from the original list, which you can do as I showed here:
https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-getting-unique-items-from-a-list/
The second part of the task is to enter your MAX formula for each unique stock number, such I showed at https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-using-vlookup-with-min-max-and-average/.
Hi Tom, how does you change the formula to select the intersecting cell that is not equal to 0?
The nature of this example is to show what is in the cell where a particular row and column intersect. Whether or not that cell contains a zero has nothing to do with how this example can be altered. What you would alternatively want to do with a conditional intersecting method is to have the condition for a non zero to occupy one of the column headers or row headers, but not the intersecting cell itself. Or, search for the first non-zero cell and return its row and column headers.
Would you happen to have an example of a formula that would be able to execute this?
Thank you very much for your quick response.
I still don’t understand. What is the “this” you want an example of? Try explaining a detailed example of what data you have in a worksheet, what the row and column headers are, and which cell you expect the formula to return a non zero.
I have a table I would like to populate. It has several headers and the rows include various currencies. I would like to retrieve the first non-zero value from a different table. For example the range of the headers in the other table is E2:R2; the column in that table containing the currencies is E2:E100. I would like to match a header (F11) and a currency (A13) in the table to populate, to the other table. Using your formula gives me the first match, however the first match is 0. I would like to get the next match, which does not equal to 0.
Hope this makes sense.
Sorry, I am just not envisioning your workbook set-up. You have a header value in F11 of which sheet. Presumably you have a header in cell E2 of another sheet. I don’t understand why if you already know the header value in cell E2 of the other sheet that you need cell F11. To keep it simple, do you want the first non zero value in range E2:E11. And does non zero mean anything other than a zero. For example, is an empty cell a zero or do you need to have a cell literally with 0 in it to be a zero. And if the format of a cell is for no decimals and the underlying value is 0.3 then the cell will display 0 but that 0.3 would be transferred looking like it should not be transferred but properly transferred because it is truly not a zero. In any case, I don’t follow what you are doing and where things are located. It helps if you go the distance with your explanation instead of just saying “other table” to specify the worksheet names that hold any range or table you refer to.
At this point, may I suggest an excellent forum for Excel questions where there are experts who visit there and volunteer their time to answer questions. I contribute to that forum as well. But I have a few projects going and there are experts around the clock who can assist faster than I can. There is no cost, it’s free and easy to register, at https://www.mrexcel.com/forum/excel-questions/.
=INDEX(A1:N19,MATCH(1,(A1:A19=A24)*(B1:B19=B24),0),MATCH(C24,$A$1:$N$1,0))
How do you write this in VBA
My first recommendation, which I do for myself because it is fast, easy, and helps me learn (or remind myself of) the syntax, is to use the macro recorder.
Enter the formula in the cell you want it to be in.
Select the cell.
Turn on the macro recorder.
Press the F2 key and then press the enter key.
Turn off the macro recorder.
You will have your answer when you see the code in your VBA module for that recorded macro.
The macro will be comprised of two lines of code.
The first line is the formula.
The second line you can probably delete, as it will be the selection under the cell your formula is in.