vlookup index match

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Vlookup, Index, Match

Vlookup, Index, Match

resolvedResolved · High Priority · Version 2016

Coralie has attended:
Excel Intermediate course
Excel Advanced course

Vlookup, Index, Match

Hi,

I have the following:
- Tab1 = bulk data that I keep adding on a monthly basis
- Tab2 = summary where I want to show only 1 month (the latest one)


TAB1, bulk data
In lines, I have a list of Countries
In columns, I have months (January, February...)
In between line and columns, I have a value. Eg:
- Bolivia, March = 30
- Bolivia, April = 40
- Bolivia, May = 40
- etc...

For other tabs, I sometimes have more variables in columns, eg
- Bolivia / tracked, March = 30
- Bolivia / untracked, March = 10
- Bolivia / tracked, April = 35
- Bolivia / untracked, April = 5

TAB2, consolidation
I have in this tab a table where a baseline data and objectives are fixed. However, in between these 2 data, I want to show what is the actual value for 1 month. EG:

1. in May, I will want to show:
- Bolivia / Tracked, Baseline = 10
- Bolivia / Tracked, MAY = 40[b]
- Bolivia / tracked, objective = 100

2. In June, I will want to show:
1. in May, I will want to show:
- Bolivia / Tracked, Baseline = 10
[b]- Bolivia / Tracked, JUNE = 60

- Bolivia / tracked, objective = 100

>> At the moment, I am using a SUMIF formula to change the month data in TAB2, and keep updating it every month.
What I want to do instead, is have a cell in TAB2 where I can change the month value (May or June...), and so it picks automatically the right data in Tab1 that belongs to the named month and the appropriate Country.

I tried to do an index/match formula, but I have difficulties to work it out.
Any way I can send you my file for support? Is the index/match the actual right formula or should/could it be something else?

Many thanks,
Coralie

RE: Vlookup, Index, Match

Hi Coralie,

Thanks you very much for your forum question. My name is Ron and I am a trainer at STL

To answer your question it would be, as you suggested, useful if you could send an example file over. If you want to send it straight to my email address then I will have a closer look and be better able to answer your questions.

My email address is:

ron.oldeboom@stl-training.co.uk

Awaiting your file

Kind regards,

Ron Oldeboom
Learning and Development Consultant
STL training

Attached files...

FLEET KPI_STL.xlsx

RE: Vlookup, Index, Match

Hi Coralie,

After a lot of failed attempts I think I finally cracked your answer. The formula is nested and quite complex. The best way to analyse it is to select cell E5 in the Duplicate sheet and then select The Formulas Tab in the ribbon  Formula Auditing group  Evaluate Formula. This will dissect your formula step by step.

In the evaluate formula tool, the underlined item is the item it will evaluate. After evaluating, the result will be shown in Italic.

I used a combination of the following functions just in case you want to see the arguments in the Help pages
SUMIF
VLOOKUP
MATCH
OFFSET

I can’t stress enough that if you had wanted to do this in Power Bi, you would have only needed to bring in the tables on the various sheets, set up a few relationships and put your dashboard together without having to do all this painful formula work.

I believe it is vital for a person in your line of work to learn Power Bi early to keep up with the changes happening in the analysis world.
STL do excellent virtual deliveries in Microsoft Teams. Feedback has been excellent from people that attended our virtual classrooms with some giving preference to virtual over face to face even.

STL runs a one day course https://www.stl-training.co.uk/syl/355/power-bi-training-courses.html which is more of an intro for people that need to work with reports but not necessarily design the reports themselves, However, for the type of work you do I would advise the 2 day course. https://www.stl-training.co.uk/cms/syllabus.php?syllabus_id=205
You can check out the links above for a course outline.

Also if you have a look at the below link, there is a little overview of the Power Bi process you might find interesting. It explains the process from connecting to data to ultimately sharing the reports with other users.
https://web.microsoftstream.com/video/7acb685a-c168-490d-81b6-189615a5bac3

I hope the formula is what you were looking for.

All the best Carolie and very nice to speak with you yesterday.

A Bientot

Ron

Attached files...

FLEET KPI_STL.xlsx

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.


 

Excel tip:

Autonumber in Excel

To create an autonumber field, can use the Offset() function.

In cell A1, enter the number 1.
Then in cell A2, enter this formula:

=OFFSET(A2,-1,0)+1

Then copy the formula from cell A2, down as far as you need.

See also: Autonumber in Excel forum post.

View all Excel hints and tips


Server loaded in 0.12 secs.