Two step lookup

[Mashtonsmith]

Ok - so I'll do my best at explaining my problem.

I need a formula that with firstly lookup a member within a table by matching to a reference number.

Once that match has been established, I then need to lookup data from a a year.

So...

The main data table looks like this..

Column 1 Column 2 Column 3 Column 4
Ref YEAR Amount 1 Amount 2
1234 2010 100 150
1234 2011 200 150
1234 2012 123 150
1234 2013 200 200
1234 2014 100 300
1234 2015 150 150
1234 2016 100 100

I want look Lookup Ref = 1234, then lookup 2012 (or any entered year) to return 123 - all in one formula

Someone has suggested Sum Ifs?

Any help appreciated.

 

[Aladin Akyurek]

In H1 control+shift+enter, not just enter:

=INDEX($C$2:$C$8,MATCH($F1,IF($B$2:$B$8=$G1,$A$2:$A$8),0))

where F1 = 1234 and G1 = 2012.

 

[BarryL]

=SUMIFS(C:C,A:A,F2,B:B,G2)

will it always be amount 1?

 

[Mashtonsmith]

No the result column (in this case C:C) will change - but I know how to do that.

Thanks - the SUMIFS worked.

I could not get the Index Match working - but thanks.

 

[BarryL]

glad to help

 

[Aladin Akyurek]

No the result column (in this case C:C) will change - but I know how to do that.

Thanks - the SUMIFS worked.

I could not get the Index Match working - but thanks.

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. When done properly, Excel itself puts a pair of { and } around the formula in recognition.

By the way, if your intent is retrieval, you should not use an aggregator.