Vlookup or index match possibly needed

**Noah101** · 11-03-2017, 11:47 PM

Good evening Programming Experts,

I need your assistance , I have an excel sheet with a dashboard on sheet1 and raw data on sheet 2. I'm trying to use a v lookup or index match but its failing, not sure if its the way my raw data set up but by the looks of it all looks good.

below is my dashboard view

02-Nov Monday
quality Metric NCC-O NCC-A ABB-D ABB-D%
Grand Total

02-Nov Monday
quality Metric NCC-O NCC-A ABB-D ABB-D%
John Doe
Amy Watson
Justin Brown
Noah Anderson

below is my raw data view

Roll UP Monday Tuesday Wednesday Thursday Friday
Met Target 22 21 16 21 22
NCC-O 16 13 21 11 9
NCC-A 23 21 32 33 16
ABB-D 14 17 22 13 22
ABB-D% 5 3 6 7 3

John Doe Monday Tuesday Wednesday Thursday Friday
Met Target 3 5 3 2 1
NCC-O 6 7 8 7 2
NCC-A 3 3 5 7 9
ABB-D 1 1 1 1 1
ABB-D% 5 6 8 9 9

Amy Watson Monday Tuesday Wednesday Thursday Friday
Met Target 3 5 3 2 1
NCC-O 6 7 8 7 2
NCC-A 3 3 5 7 9
ABB-D 1 1 1 1 1
ABB-D% 5 6 8 9 9

Justin Brown Monday Tuesday Wednesday Thursday Friday
Met Target 3 5 3 2 1
NCC-O 6 7 8 7 2
NCC-A 3 3 5 7 9
ABB-D 1 1 1 1 1
ABB-D% 5 6 8 9 9

Noah Anderson Monday Tuesday Wednesday Thursday Friday
Met Target 3 5 3 2 1
NCC-O 6 7 8 7 2
NCC-A 3 3 5 7 9
ABB-D 1 1 1 1 1
ABB-D% 5 6 8 9 9

I would like the raw nicely summarized in the dashboard. The only thing that I would need say is my dashboard DAY OF WEEK is programmed to change each day so it will become Monday then Tuesday then Wednesday ect. I want the formula to capture that in the dashboard from the raw data as the days change,

I have also attached my excel document to make it easier.

Thank you greatly.

**protonLeah** · 11-04-2017, 01:08 AM

Create named ranges for each block on raw data sheet
Amy_Watson

='Raw data'!$B$17:$G$22

Grand_Total

='Raw data'!$B$2:$G$7

John_Doe

='Raw data'!$B$10:$G$15

Justin_Brown

='Raw data'!$B$25:$G$30

Noah_Anderson

='Raw data'!$B$34:$G$39

Use:

Please Login or Register  to view this content.

on your dashboard sheet.
The SUBSTITUTE fn replaces the spaces in the names with underscores, and passes the result to INDIRECT to build the array to return the data from corresponding to the employee name.

**kvsrinivasamurthy** · 11-04-2017, 01:56 AM

In C14, drag upto F14

=INDEX('Raw data'!$C$3:$G$7,MATCH(Dashboard!C$13,'Raw data'!$B$3:$B$7,0),MATCH($C$18,'Raw data'!$C$10:$G$10,0))

In C20, Drag upto F23

=INDEX('Raw data'!$C$10:$G$39,MATCH(Dashboard!C$19,INDEX('Raw data'!$B$10:$B$39,MATCH(Dashboard!$B20,'Raw data'!$B$10:$B$39,0)):'Raw data'!$B$39,0),MATCH($C$18,'Raw data'!$C$10:$G$10,0))

**shukla.ankur281190** · 11-04-2017, 02:12 AM

Can try

C20

Formula:

Please Login or Register  to view this content.

Copy across.

**AliGW** · 11-04-2017, 02:12 AM

Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

Use concise, accurate thread titles.
Your post title should describe your problem, not your anticipated solution.
Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

**Noah101** · 11-04-2017, 08:49 PM

Thank you very much experts shukla.ankur281190 ,protonLeah and kvsrinivasamurthy , I found that all of your formulas do work! and solved by issue! I think the one from protonLeah worked a little better due to the use of the indirect and substitute which assisted me in being able to drag my formula In C20 to F23 without changing the parameters from ='Raw data'!$B$10:$G$15 to ='Raw data'!$B$25:$G$30 ect.

Thank you all, you have been a great help!

Noah

**AliGW** · 11-05-2017, 02:48 AM

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification.

All participants:
Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.

Vlookup or index match possibly needed

LinkBack

Thread Tools

Display

Vlookup or index match possibly needed

Re: Vlookup or index match possibly needed

Re: Vlookup or index match possibly needed

Re: Vlookup or index match possibly needed

Re: Vlookup or index match possibly needed

Re: Vlookup or index match possibly needed

Re: Vlookup or index match possibly needed

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index Match / Vlookup / Other formula needed

[SOLVED] Need help with Index Match or possibly array type of match

[SOLVED] VLOOKUP formula with multiple criteria (possibly INDEX/MATCH instead?)

[SOLVED] Sumproduct formula needed (possibly using index/match)

Vlookup with index/match possibly.

[SOLVED] Vlookup or Index Match Formula Needed

[SOLVED] Index, Match, Vlookup, Hlookup help needed

Bookmarks

Bookmarks

Posting Permissions