Two step lookup

Mashtonsmith

Board Regular
Joined
Oct 1, 2004
Messages
164
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Mashtonsmith

Board Regular
Joined
Oct 1, 2004
Messages
164
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,658
Members
414,325
Latest member
kfg1287

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
Top