+ Reply to Thread
Results 1 to 16 of 16

Index Match formula for dashboard worksheet

  1. #1
    Registered User
    Join Date
    09-13-2016
    Location
    Cesenatico, Italy
    MS-Off Ver
    365
    Posts
    16

    Index Match formula for dashboard worksheet

    I'm working on a database consisting of a data sheet and a dashboard. For the dashboard, I would like to have a grid looking table to show the various specifications of each unit. Instead of slicers, I would like to use formulas and am trying to use the Index Match formula, which I am not very familiar with.

    The formula currently drafted does not return any result:

    =INDEX(Data!$A$2:$AQ$45,MATCH(Dashboard!$B$6,Data!$E$2:$E$45))

    Can you please help me identify what is wrong with the above formula?

    Thank you for your help.

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Index Match formula for dashboard worksheet



    =INDEX(Data!$A$2:$AQ$45,MATCH(Dashboard!$B$6,Data!$E$2:$E$45,0))

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index Match formula for dashboard worksheet

    Quote Originally Posted by SurfexcelIT View Post
    The formula currently drafted does not return any result:

    =INDEX(Data!$A$2:$AQ$45,MATCH(Dashboard!$B$6,Data!$E$2:$E$45))

    Can you please help me identify what is wrong with the above formula?
    There are a couple of issues.

    You need to define the column number argument. The range A$2:$AQ$45 consists of 43 columns so the column number argument has to resolve to a number from 1 to 43.

    You've defined the row number argument with the MATCH function but that syntax is looking for an approximate match not an "exact" match. Is that your intention?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    09-13-2016
    Location
    Cesenatico, Italy
    MS-Off Ver
    365
    Posts
    16

    Re: Index Match formula for dashboard worksheet

    I have updated the formula as follows, however still get an error result. Not sure how to define the column number argument...

    =INDEX(Data!$A$2:$AQ$45,5,MATCH(Dashboard!$B$6,Data!$E$2:$E$45,0))

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index Match formula for dashboard worksheet

    I think it's about time that you post the file (or a small sample file) so we can see what you're trying to do.

    Make sure you clearly show us what result you expect.
    Last edited by Tony Valko; 10-22-2016 at 04:35 PM.

  6. #6
    Registered User
    Join Date
    09-13-2016
    Location
    Cesenatico, Italy
    MS-Off Ver
    365
    Posts
    16

    Re: Index Match formula for dashboard worksheet

    Here is the file.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    414

    Re: Index Match formula for dashboard worksheet

    The formula in H8 ;

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Index Match formula for dashboard worksheet

    Try
    =INDEX(Data!$A$2:$AQ$45,5,MATCH(Dashboard!$B$6,Data!$D$2:$D$45,0))

    =INDEX(Data!$A$2:$AQ$45,5,MATCH(Dashboard!$B$6,Data!$E$2:$E$45,0)) wrong part in your formula coded as red.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  9. #9
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Index Match formula for dashboard worksheet

    Change:

    =INDEX(Data!$A$2:$AQ$45,5,MATCH(Dashboard!$B$6,Data!$E$2:$E$45,0))

    To:

    =INDEX(Data!$A$2:$AQ$45,MATCH(Dashboard!$B$6,Data!$D$2:$D$45,0),5)

  10. #10
    Registered User
    Join Date
    09-13-2016
    Location
    Cesenatico, Italy
    MS-Off Ver
    365
    Posts
    16

    Re: Index Match formula for dashboard worksheet

    Chullan88: thank you, it works. However, can you please explain why I am using the D column in the formula parameters, when the data I am looking for is in column E? Thanks again.

  11. #11
    Registered User
    Join Date
    09-13-2016
    Location
    Cesenatico, Italy
    MS-Off Ver
    365
    Posts
    16

    Re: Index Match formula for dashboard worksheet

    Thank you Ankur, this works! Kind regards,

  12. #12
    Registered User
    Join Date
    09-13-2016
    Location
    Cesenatico, Italy
    MS-Off Ver
    365
    Posts
    16

    Re: Index Match formula for dashboard worksheet

    Phuocam, thank you. It works. Same question as posed to chullan88, why am I using column D instead of E? Also, can you please explain what the 5 at the end of your revised formula is for?
    =INDEX(Data!$A$2:$AQ$45,MATCH(Dashboard!$B$6,Data!$D$2:$D$45,0),5)

  13. #13
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Index Match formula for dashboard worksheet

    Quote Originally Posted by SurfexcelIT View Post
    Phuocam, thank you. It works. Same question as posed to chullan88, why am I using column D instead of E? Also, can you please explain what the 5 at the end of your revised formula is for?
    =INDEX(Data!$A$2:$AQ$45,MATCH(Dashboard!$B$6,Data!$D$2:$D$45,0),5)
    @Surfexcel you must see the screen tip below the formula running while you try to put any function.

    You have to look match function there is three arguments which need to fulfil for getting accurate result. Let me show you.

    MATCH( value, array, [match_type] )

    Value is your searching value , array is - that range where search value should be and the last part - which is type , if you haven't much confidence put 0 for exact match.

    Better understanding look at this web page with description of Match function.

    https://www.techonthenet.com/excel/formulas/match.php

  14. #14
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    414

    Re: Index Match formula for dashboard worksheet

    Quote Originally Posted by SurfexcelIT View Post
    Chullan88: thank you, it works. However, can you please explain why I am using the D column in the formula parameters, when the data I am looking for is in column E? Thanks again.
    The data you're looking for, is in E column,that is referenced to in the first argument of INDEX.
    MATCH function is trying to match and find the position of Dashboard!B6 in Column D.
    Hope its clear.


    Regards

  15. #15
    Registered User
    Join Date
    09-13-2016
    Location
    Cesenatico, Italy
    MS-Off Ver
    365
    Posts
    16

    Re: Index Match formula for dashboard worksheet

    Thank you sir

  16. #16
    Registered User
    Join Date
    09-13-2016
    Location
    Cesenatico, Italy
    MS-Off Ver
    365
    Posts
    16

    Re: Index Match formula for dashboard worksheet

    Thank you. Kind regards,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-16-2015, 03:59 PM
  2. Hyperlinks in a dashboard (MATCH and INDEX)
    By ESKR in forum Office 365
    Replies: 0
    Last Post: 11-14-2014, 09:55 AM
  3. Replies: 9
    Last Post: 11-07-2014, 07:10 AM
  4. [SOLVED] Creating a Dashboard with Vlookup (or Match/Index)
    By rstarr1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2014, 02:06 PM
  5. Replies: 3
    Last Post: 02-21-2014, 10:02 AM
  6. Error in Index & Match Formula due to Defined Names Worksheet is Getting Changed!
    By Rajeshkumar R in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2013, 08:37 AM
  7. Replies: 2
    Last Post: 01-13-2012, 05:21 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1