Help with Formula - Two criteria

dameindistress

New Member
Joined
Feb 26, 2008
Messages
10
Hi All,

Thanks for helping in advance. I am trying to write a formula that uses two criteria to return a value. I've trawled the net for a solution or previous responses, however I cannot replicate them to a satisfactory result.

I need a formula that checks Criteria A, as well as Criteria B (that is years are staggered from one worksheet to the next) to give the results in italics included below. I can get the formula to work with a SUMIF statement, but only against Criteria A, or B, not both. I have tried SUMIFS, SUMPRODUCT, etc statements to no avail. I am not supposed to use arrays, so need to by-pass that option if that's what you were thinking...

[TABLE="width: 1088"]
<colgroup><col span="17"></colgroup><tbody>[TR]
[TD]Criteria A:[/TD]
[TD]Delta[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2001[/TD]
[TD="align: right"]2002[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[/TR]
[TR]
[TD]Alfa[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Gamma[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Criteria B:[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

I need the formula to look up "Delta" in the table of data, and then look up the years and return a result in the appropriate year, of the appropriate category. In red italics is the result I would expect.

Thank you so much for any help, and apologies if my "forum etiquette" and questioning does not conform or is borderline incomprehensible.

DiD :)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
This appears a look up problem...

In B10 enter and copy across:

=IFNA(VLOOKUP($B$1,$A$3:$Q$7,MATCH(B$9,INDEX($A$3:$Q$7,1,0),0),0),"")

If you get a #NAME ? error, replace IFNA with IFERROR in the formula.

where B1 = Delta and B$9 = 2004
 
Upvote 0
That's excellent. Thanks for the prompt reply and it works a treat. You're a legend, but.....
I've now been thrown a couple more curve-balls...
I now have a third criteria to add. That is, if it's Delta, And Epsilon, then what would the formula be; keeping in mind that Epsilon may or may not appear in the list, but if it does, then it needs to meet that criteria and the Delta/years criteria. Tricky?
On top of that, my current employer does not allow IfError formulas (even though your previous solution did not require my use of IfError). Please see below same as yesterday, only with additions.

[TABLE="width: 1152"]
<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Criteria A:[/TD]
[TD="width: 64"]Delta[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Criteria B:[/TD]
[TD]Epsilon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl63, align: right"]2001[/TD]
[TD="class: xl63, align: right"]2002[/TD]
[TD="class: xl63, align: right"]2003[/TD]
[TD="class: xl63, align: right"]2004[/TD]
[TD="class: xl63, align: right"]2005[/TD]
[TD="class: xl63, align: right"]2006[/TD]
[TD="class: xl63, align: right"]2007[/TD]
[TD="class: xl63, align: right"]2008[/TD]
[TD="class: xl63, align: right"]2009[/TD]
[TD="class: xl63, align: right"]2010[/TD]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63, align: right"]2012[/TD]
[TD="class: xl63, align: right"]2013[/TD]
[TD="class: xl63, align: right"]2014[/TD]
[TD="class: xl63, align: right"]2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Alfa[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Beta[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gamma[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Epsilon[/TD]
[TD]Delta[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Criteria C:[/TD]
[TD="class: xl63, align: right"]2004[/TD]
[TD="class: xl63, align: right"]2005[/TD]
[TD="class: xl63, align: right"]2006[/TD]
[TD="class: xl63, align: right"]2007[/TD]
[TD="class: xl63, align: right"]2008[/TD]
[TD="class: xl63, align: right"]2009[/TD]
[TD="class: xl63, align: right"]2010[/TD]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63, align: right"]2012[/TD]
[TD="class: xl63, align: right"]2013[/TD]
[TD="class: xl63, align: right"]2014[/TD]
[TD="class: xl63, align: right"]2015[/TD]
[TD="class: xl63, align: right"]2016[/TD]
[TD="class: xl63, align: right"]2017[/TD]
[TD="class: xl63, align: right"]2018[/TD]
[TD="class: xl63, align: right"]2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]400[/TD]
[TD="class: xl64, align: right"]400[/TD]
[TD="class: xl64, align: right"]400[/TD]
[TD="class: xl64, align: right"]400[/TD]
[TD="class: xl64, align: right"]400[/TD]
[TD="class: xl64, align: right"]400[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Thank you all in advance, especially Aladin for his previous solution!

DiD.
 
Upvote 0
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][td]
L​
[/td][td]
M​
[/td][td]
N​
[/td][td]
O​
[/td][td]
P​
[/td][td]
Q​
[/td][td]
R​
[/td][/tr][tr][td]
1​
[/td][td][/td][td]Criteria A:[/td][td]Delta[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
2​
[/td][td][/td][td]Criteria B:[/td][td]Epsilon[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
3​
[/td][td][/td][td][/td][td]
2000
[/td][td]
2001
[/td][td]
2002
[/td][td]
2003
[/td][td]
2004
[/td][td]
2005
[/td][td]
2006
[/td][td]
2007
[/td][td]
2008
[/td][td]
2009
[/td][td]
2010
[/td][td]
2011
[/td][td]
2012
[/td][td]
2013
[/td][td]
2014
[/td][td]
2015
[/td][/tr]
[tr][td]
4​
[/td][td][/td][td]Alfa[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
200
[/td][td]
100
[/td][td]
100
[/td][td]
100
[/td][td]
100
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][/tr]
[tr][td]
5​
[/td][td][/td][td]Beta[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
900
[/td][td]
200
[/td][td]
200
[/td][td]
200
[/td][td]
200
[/td][td]
200
[/td][td]
200
[/td][td]
200
[/td][td]
200
[/td][td]
200
[/td][td]
0
[/td][td]
0
[/td][/tr]
[tr][td]
6​
[/td][td][/td][td]Gamma[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
300
[/td][td]
300
[/td][td]
300
[/td][td]
300
[/td][td]
300
[/td][td]
300
[/td][/tr]
[tr][td]
7​
[/td][td]Epsilon[/td][td]Delta[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
300
[/td][td]
0
[/td][td]
400
[/td][td]
400
[/td][td]
400
[/td][td]
400
[/td][td]
400
[/td][td]
400
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][/tr]
[tr][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td][/td][td]Criteria C:[/td][td]
2004
[/td][td]
2005
[/td][td]
2006
[/td][td]
2007
[/td][td]
2008
[/td][td]
2009
[/td][td]
2010
[/td][td]
2011
[/td][td]
2012
[/td][td]
2013
[/td][td]
2014
[/td][td]
2015
[/td][td]
2016
[/td][td]
2017
[/td][td]
2018
[/td][td]
2019
[/td][/tr]
[tr][td]
10​
[/td][td][/td][td]Result[/td][td]
300
[/td][td]
0
[/td][td]
400
[/td][td]
400
[/td][td]
400
[/td][td]
400
[/td][td]
400
[/td][td]
400
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


In C10 control+shift+enter, not just enter, and copy across:

=IFNA(INDEX($C$4:$R$7,IF($C$2="",MATCH($C$1,$B$4:$B$7,0),MATCH($C$2,IF($B$4:$B$7=$C$1,$A$4:$A$7),0)),MATCH(C$9,$C$3:$R$3,0)),"")
 
Upvote 0
Aladin comes to the rescue again.

Many thanks for your continued assistance. I tried the first solution this morning and seemed to do the right thing, now I think I see the issue. That is... The C1 and C2 will actually always have a value, it is in fact the data that may not have either. To be fair I solved one of the criteria by manipulating the way the data was reported from a different software platform To be more transparent, here is your kind table...

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[/TR]
[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[TD]
J​
[/TD]
[TD]
K​
[/TD]
[TD]
L​
[/TD]
[TD]
M​
[/TD]
[TD]
N​
[/TD]
[TD]
O​
[/TD]
[TD]
P​
[/TD]
[TD]
Q​
[/TD]
[TD]
R​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD][/TD]
[TD]Criteria A:[/TD]
[TD]Delta[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2000
[/TD]
[TD]
2001
[/TD]
[TD]
2002
[/TD]
[TD]
2003
[/TD]
[TD]
2004
[/TD]
[TD]
2005
[/TD]
[TD]
2006
[/TD]
[TD]
2007
[/TD]
[TD]
2008
[/TD]
[TD]
2009
[/TD]
[TD]
2010
[/TD]
[TD]
2011
[/TD]
[TD]
2012
[/TD]
[TD]
2013
[/TD]
[TD]
2014
[/TD]
[TD]
2015
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD][/TD]
[TD]Alfa[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
200
[/TD]
[TD]
100
[/TD]
[TD]
100
[/TD]
[TD]
100
[/TD]
[TD]
100
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
900
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD][/TD]
[TD]Gamma[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD][/TD]
[TD]Criteria B:[/TD]
[TD]
2004
[/TD]
[TD]
2005
[/TD]
[TD]
2006
[/TD]
[TD]
2007
[/TD]
[TD]
2008
[/TD]
[TD]
2009
[/TD]
[TD]
2010
[/TD]
[TD]
2011
[/TD]
[TD]
2012
[/TD]
[TD]
2013
[/TD]
[TD]
2014
[/TD]
[TD]
2015
[/TD]
[TD]
2016
[/TD]
[TD]
2017
[/TD]
[TD]
2018
[/TD]
[TD]
2019
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD][/TD]
[TD]Result[/TD]
[TD]
0
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Having tested both your formulas, I think I see that the "" in the formula is essentially saying if Criteria A or B cell is blank, then return a respective blank. I think my new table explains it better, that is, if the data has no "Delta" then give me zero (as new table shows), otherwise give me whichever category I demand (Alfa, Beta, Gamma, Delta) and in the appropriate year, as long as that category exists in the B4:B7 section. For exapmple, if I enter Alfa, it returns 200,100,100,100,100 from 2004 onwards, as long as Alfa is in the B4:B7 column. If it isn't then return 0.

Sorry again, my bad. I'm a novice at these boards, but all and any help that can be given will get me a step closer to solving this irritating riddle.

Thank you all, and especially Aladin.

DiD.
 
Upvote 0
Hi Aladin,

Thanks again. After looking at this again, I've solved one of the criteria issues, but not the other. I think I misrepresented my original request after having tested your formula. Forgetting Criteria C for now, as I've solved that one via how the data is reported from the other software, I actually meant that the blanks would be in the data, rather than the criteria cell (C1 and C2). That is, if I have "Delta" in C1, "Delta" may not actually be in B4:B7. As below with expected outcome...

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[/TR]
[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[TD]
J​
[/TD]
[TD]
K​
[/TD]
[TD]
L​
[/TD]
[TD]
M​
[/TD]
[TD]
N​
[/TD]
[TD]
O​
[/TD]
[TD]
P​
[/TD]
[TD]
Q​
[/TD]
[TD]
R​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD][/TD]
[TD]Criteria A:[/TD]
[TD]Delta[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2000
[/TD]
[TD]
2001
[/TD]
[TD]
2002
[/TD]
[TD]
2003
[/TD]
[TD]
2004
[/TD]
[TD]
2005
[/TD]
[TD]
2006
[/TD]
[TD]
2007
[/TD]
[TD]
2008
[/TD]
[TD]
2009
[/TD]
[TD]
2010
[/TD]
[TD]
2011
[/TD]
[TD]
2012
[/TD]
[TD]
2013
[/TD]
[TD]
2014
[/TD]
[TD]
2015
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD][/TD]
[TD]Alfa[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
200
[/TD]
[TD]
100
[/TD]
[TD]
100
[/TD]
[TD]
100
[/TD]
[TD]
100
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
900
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
200
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD][/TD]
[TD]Gamma[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
0
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[TD]
300
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD][/TD]
[TD]Criteria B:[/TD]
[TD]
2004
[/TD]
[TD]
2005
[/TD]
[TD]
2006
[/TD]
[TD]
2007
[/TD]
[TD]
2008
[/TD]
[TD]
2009
[/TD]
[TD]
2010
[/TD]
[TD]
2011
[/TD]
[TD]
2012
[/TD]
[TD]
2013
[/TD]
[TD]
2014
[/TD]
[TD]
2015
[/TD]
[TD]
2016
[/TD]
[TD]
2017
[/TD]
[TD]
2018
[/TD]
[TD]
2019
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD][/TD]
[TD]Result[/TD]
[TD]
0
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

For example, if no "Delta" appears in the data, zeros will be returned. If a new set of data should then look like the original, the result would be as...

[TABLE="class: cms_table, width: 1088"]
<tbody>[TR]
[TD="align: right"]2000[/TD]
[TD="align: right"]2001[/TD]
[TD="align: right"]2002[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015 [/TD]
[/TR]
[TR]
[TD]Alfa[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Gamma[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Criteria B:[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Hope this makes better sense, and apologies again for shifting and changing (sometimes bosses don't know what they want and we're stuck wondering). So I think this should make the formula more straight-forward? Should be easier to look for a category, if that category is in the data, return it in the correct year, otherwise leave a zero.

Many many thanks all, most importantly Aladin!

DiD.
 
Upvote 0
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][/tr][tr][td]
1​
[/td][td] Criteria A:[/td][td] Delta[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]
2​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]
3​
[/td][td] [/td][td]
2000
[/td][td]
2001
[/td][td]
2002
[/td][td]
2003
[/td][td]
2004
[/td][td]
2005
[/td][td]
2006
[/td][td]
2007
[/td][td]
2008
[/td][/tr]
[tr][td]
4​
[/td][td] Alfa[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
200
[/td][td]
100
[/td][td]
100
[/td][td]
100
[/td][td]
100
[/td][/tr]
[tr][td]
5​
[/td][td] Beta[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
900
[/td][td]
200
[/td][td]
200
[/td][td]
200
[/td][td]
200
[/td][/tr]
[tr][td]
6​
[/td][td] Gamma[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][/tr]
[tr][td]
7​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]
8​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]
9​
[/td][td] Criteria B:[/td][td]
2004
[/td][td]
2005
[/td][td]
2006
[/td][td]
2007
[/td][td]
2008
[/td][td]
2009
[/td][td]
2010
[/td][td]
2011
[/td][td]
2012
[/td][/tr]
[tr][td]
10​
[/td][td] Result[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][td]
0
[/td][/tr]
[/table]


In C10 enter and copy across:

=IFNA(VLOOKUP($C$1,$B$3:$R$7,MATCH(C$9,INDEX($B$3:$R$7,1,0),0),0),0)
 
Upvote 0
WOW. What an Excel legend. It works like an absolute treat. I am indebted to you.

Thank you, thank you, thank you.

PS: I see you are in The Hague. Do you perhaps work for a multinational oil company, if I may be so bold as to ask?
 
Upvote 0
WOW. What an Excel legend. It works like an absolute treat. I am indebted to you.

Thank you, thank you, thank you.

PS: I see you are in The Hague. Do you perhaps work for a multinational oil company, if I may be so bold as to ask?

Glad I could help. No, not that. I teach at De Haagse Hogeschool in The Hague.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top