Help with Formula - Two criteria

[dameindistress]

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

Criteria A:	Delta
	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015
Alfa	0	0	0	0	100	100	100	100	100	0	0	0	0	0	0	0
Beta	0	0	0	0	200	200	200	200	200	200	200	200	200	200	0	0
Gamma	0	0	0	0	0	0	0	0	0	0	300	300	300	300	300	300
Delta	0	0	0	0	0	0	400	400	400	400	400	400	0	0	0	0
Criteria B:	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019
Result	0	0	400	400	400	400	400	0	0	0	0	0	0	0	0	0

<colgroup><col span="17"></colgroup><tbody>
</tbody>

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

 

[Aladin Akyurek]

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

 

[dameindistress]

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.

	Criteria A:	Delta
	Criteria B:	Epsilon
		2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015
	Alfa	0	0	0	0	100	100	100	100	100	0	0	0	0	0	0	0
	Beta	0	0	0	0	200	200	200	200	200	200	200	200	200	0	0
	Gamma	0	0	0	0	0	0	0	0	0	0	300	300	300	300	300	300
Epsilon	Delta	0	0	0	0	0	0	400	400	400	400	400	400	0	0	0	0
	Criteria C:	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019
		0	0	400	400	400	400	400	400	0	0	0	0	0	0	0	0

<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>
</tbody>

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

DiD.

 

[Aladin Akyurek]

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

1​

Criteria A:

Delta

2​

Criteria B:

Epsilon

3​

2000​

2001​

2002​

2003​

2004​

2005​

2006​

2007​

2008​

2009​

2010​

2011​

2012​

2013​

2014​

2015​

4​

Alfa

0​

0​

0​

0​

200​

100​

100​

100​

100​

0​

0​

0​

0​

0​

0​

0​

5​

Beta

0​

0​

0​

0​

900​

200​

200​

200​

200​

200​

200​

200​

200​

200​

0​

0​

6​

Gamma

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

300​

300​

300​

300​

300​

300​

7​

Epsilon

Delta

0​

0​

0​

0​

300​

0​

400​

400​

400​

400​

400​

400​

0​

0​

0​

0​

8​

9​

Criteria C:

2004​

2005​

2006​

2007​

2008​

2009​

2010​

2011​

2012​

2013​

2014​

2015​

2016​

2017​

2018​

2019​

10​

Result

300​

0​

400​

400​

400​

400​

400​

400​

0​

0​

0​

0​

11​

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)),"")

 

[dameindistress]

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

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

1​

Criteria A:

Delta

2​

3​

2000​

2001​

2002​

2003​

2004​

2005​

2006​

2007​

2008​

2009​

2010​

2011​

2012​

2013​

2014​

2015​

4​

Alfa

0​

0​

0​

0​

200​

100​

100​

100​

100​

0​

0​

0​

0​

0​

0​

0​

5​

Beta

0​

0​

0​

0​

900​

200​

200​

200​

200​

200​

200​

200​

200​

200​

0​

0​

6​

Gamma

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

300​

300​

300​

300​

300​

300​

7​

8​

9​

Criteria B:

2004​

2005​

2006​

2007​

2008​

2009​

2010​

2011​

2012​

2013​

2014​

2015​

2016​

2017​

2018​

2019​

10​

Result

0​

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

11​

<tbody>
</tbody>

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.

 

[dameindistress]

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

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

1​

Criteria A:

Delta

2​

3​

2000​

2001​

2002​

2003​

2004​

2005​

2006​

2007​

2008​

2009​

2010​

2011​

2012​

2013​

2014​

2015​

4​

Alfa

0​

0​

0​

0​

200​

100​

100​

100​

100​

0​

0​

0​

0​

0​

0​

0​

5​

Beta

0​

0​

0​

0​

900​

200​

200​

200​

200​

200​

200​

200​

200​

200​

0​

0​

6​

Gamma

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

300​

300​

300​

300​

300​

300​

7​

8​

9​

Criteria B:

2004​

2005​

2006​

2007​

2008​

2009​

2010​

2011​

2012​

2013​

2014​

2015​

2016​

2017​

2018​

2019​

10​

Result

0​

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

11​

<tbody>
</tbody>

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

2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015
Alfa	0	0	0	0	100	100	100	100	100	0	0	0	0	0	0	0
Beta	0	0	0	0	200	200	200	200	200	200	200	200	200	200	0	0
Gamma	0	0	0	0	0	0	0	0	0	0	300	300	300	300	300	300
Delta	0	0	0	0	0	0	400	400	400	400	400	400	0	0	0	0
Criteria B:	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019
Result	0	0	400	400	400	400	400	0	0	0	0	0	0	0	0	0

<tbody>
</tbody>

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.

 

[Aladin Akyurek]

Row\Col	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Criteria A:	Delta
2​
3​		2000​	2001​	2002​	2003​	2004​	2005​	2006​	2007​	2008​
4​	Alfa	0​	0​	0​	0​	200​	100​	100​	100​	100​
5​	Beta	0​	0​	0​	0​	900​	200​	200​	200​	200​
6​	Gamma	0​	0​	0​	0​	0​	0​	0​	0​	0​
7​
8​
9​	Criteria B:	2004​	2005​	2006​	2007​	2008​	2009​	2010​	2011​	2012​
10​	Result	0​	0​	0​	0​	0​	0​	0​	0​	0​

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)

 

[dameindistress]

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?

 

[Aladin Akyurek]

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.

 

[dameindistress]

Well, lucky students then!

Many thanks again.