This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello,
I have a table where it appeaqrs that I will have to lookup values horizontally, My problem is that I have multiple criteria in order to get the value I am looking for. I'm thnking that I will need a combination of the Hlookup with the Index/Match functionality but I'm not well versed with the Index/Match functionality. I have been googling this for several hours and have come up with nothing. Following is part of the table and the values from the multiple criteria I am looking for. Your help is greatly appreciated. Thanks in advance!
Criteria
AP Nbr LI Part Code Formula (This data is located in columns K-M)
G655 B8253 PTM The value based on these 3 criteria should be: $17,000
G655 B8249 CFO The value based on these 3 criteria should be: $29,207
Lookup Table (located in columns A-I)
| AP Nbr | LI Part | CHR | PTM | FRT | FPE | EAT | DNB | CFO |
|---|---|---|---|---|---|---|---|---|
| G655 | B8248 | $13,874 | $9,500 | $100,000 | $15,000 | $34,000 | $568,900 | $41,205 |
| G655 | B8249 | $9,834 | $9,500 | $100,000 | $15,000 | $23,000 | $575,200 | $29,207 |
| G655 | B8250 | $2,309 | $2,580 | $0 | $15,000 | $0 | $550,900 | $19,125 |
| G655 | B8252 | $8,080 | $17,000 | $0 | $15,000 | $14,000 | $550,900 | $23,996 |
| G655 | B8253 | $13,030 | $17,000 | $0 | $15,000 | $14,000 | $550,900 | $34,828 |
| G655 | B8254 | $13,030 | $17,000 | $0 | $15,000 | $26,000 | $550,900 | $34,828 |
| G655 | B8262 | $11,922 | $34,000 | $100,000 | $15,000 | $14,000 | $550,900 | $32,408 |
| G655 | B8264 | $2,369 | $2,580 | $100,000 | $15,000 | $0 | $550,900 | $19,125 |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Although solution has been provided, just as an alternate solution I used sumifs and offset.
=SUMIFS(OFFSET($C$2:$C$9,0,MATCH(M2,$C$1:$I$1,0)-1),$A$2:$A$9,K2,$B$2:$B$9,L2)
Answer accepted by question author
Hello,
Criteria
AP Nbr LI Part Code Formula (This data is located in columns K-M)
G655 B8253 PTM The value based on these 3 criteria should be: $17,000
G655 B8249 CFO The value based on these 3 criteria should be: $29,207
The one thing that I did was to range name each of the "Codes" seven in your example
from CHR TO CFO
=SUMPRODUCT(--($A$2:$A$1000=K2),--($B$2:$B$1000=L2),INDIRECT(M2))
I'm sure that your ranges will be different will be different. You need ot make sure that the range dimension of the seven codes match the first two items in the sumproduct - example D2:D1000 would define PTM
This also that your summary starts in column K
| AP Nbr | LI Part | Code | Total |
|---|---|---|---|
| G655 | B8249 | PTM | 9500 |
Answer accepted by question author
Hi,
I assume here that the values you're looking up are properly formatted numbers. Try this
=SUMPRODUCT(--($A$2:$A$9=K2),--($B$2:$B$9=L2),INDEX($C$2:$I$9,,MATCH(M2,$C$1:$I$1,0)))
Hi Brad,
I'd like to thank you for helping me with this problem. I've been working on it for several hours with no resolution. Yes, I will take your suggestion and use Mike's solution. I'm on a contract and am trying to make this app as child proof as I can. I must tell you that I have used the Indirect function in the past but never in this context. Thanks for the lesson it was a great learning experience for me. By the way, I gave you all of the kudo's I could. Thanks again!
John
=SUMPRODUCT(--($A$2:$A$1000=K2),--($B$2:$B$1000=L2),INDIRECT(M2))
For what it is worth - I'd use Mike's formula (since it doesn't use the indirect function and you don't have to name the ranges), but both will work.