3 dimension look up

Resolved · Urgent Priority · Version 2016 (Mac)

Mon 11 Sep 2017, 19:06 Delegate Clélia-elsa said...

Clélia-elsa has attended:
Excel Advanced course

3 dimension look up

Hi

I recently attended the advanced excel training and have a question related to 3 dimension look up.

I have data ordered in a table in this way.

Column A contains countries
Column B contains metric
Column C contains year
Column D contains the unique value

There are 5,000 rows of data and I need a solution to look up values, asking excel 3 variables (country, metric and year) to get the value.

Using index and match, which formula can I use ?

Many thanks

Tue 12 Sep 2017, 10:53 Edited on Tue 12 Sep 2017, 10:55 Trainer Jens said...

RE: 3 dimension look up

Hi Clélia-elsa,

Thank you for the forum question.

I have attached an example file where you can see two options.

You may need a Sumifs function or turn a Index & Match function into an array formula.

Have a look at my example and please let me know if it makes sense.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

3d lookup.xlsx

Tue 12 Sep 2017, 12:55 Delegate Clélia-elsa said...

RE: 3 dimension look up

Thanks! I worked out an array formula using index and match last night but I really like the sumif formula!

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Display developer tab - Excel 2010

a. In Excel, click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the box next to Developer in the list of tabs on the right hand side of the dialog box. When ticked the Developer tab will be visible.
e. Click OK to apply your changes

View all Excel hints and tips