+ Reply to Thread
Results 1 to 4 of 4

Creating a Dashboard with Vlookup (or Match/Index)

  1. #1
    Registered User
    Join Date
    01-08-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    9

    Creating a Dashboard with Vlookup (or Match/Index)

    Hi Everyone.

    I've been searching and can't seem to find a formula that does a vlookup from one sheet to another and replaces the actual (true value) responses with "X", and a blank with a blank (Not "#N/A).

    I've tried this (see dashboard cell: C4:D4), but it gives me the result as the value, not X. And a blank as "0": =IF(A4="","",IF(ISERROR(VLOOKUP(A4,'Completed Skills'!A:C,3,0)),REPT("X",1),VLOOKUP(Dashboard!A4,'Completed Skills'!A:C,3,0)))

    I also tried this (see dashboard cell: C5:D5), but it only gives me "X" as the result =IF(ISERROR(VLOOKUP(A5,'Completed Skills'!A:C,3,0))," ",REPT("X",1))

    The response I'm looking to replace is "Completed" with "X" and blank with blank


    Attached is a workbook with both pages if that helps to clarify.
    Oh, and if this is simpler with match/index, I'm open to exploring that, but i've never used it.

    Oh, and bonus points for if you can populate A4:A23 automatically on the "Dashboard" when we filter column "A" on the "Completed Skills" page


    I know that's a lot, if you have any questions or would like some clarity, please let me know.
    I think we're close, but just not quite there. Any help is much appreciated.
    HHmmmm??

    Thanks everyone!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Creating a Dashboard with Vlookup (or Match/Index)

    Try this in cell C4 and copy throughout

    =IF(INDEX('Completed Skills'!$C$3:$I$22,MATCH('Completed Skills'!$A3,Dashboard!$A$4:$A$23,0),MATCH(C$3,'Completed Skills'!$C$2:$I$2,0))="Completed","X","")


    Another alternative is as it looks like the completed skills and dashboard pages are the same, is use "Find and replace" and replace all occurrences of "Completed" with "X"
    Last edited by Speshul; 10-02-2014 at 12:41 PM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Registered User
    Join Date
    01-08-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Creating a Dashboard with Vlookup (or Match/Index)

    Thank you Speshul. Let me add one more hitch. On the "Dashboard" instead of the Header (C3:I3) reading the same as on the "Completed Skills" - How would the formula change if we used abbreviations on the dashboard, T1, T2, T3 instead of Training 1, Training 2, Training 3.

    Thanks, and sorry for the error on my part.

  4. #4
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Creating a Dashboard with Vlookup (or Match/Index)

    If they will always be in the same order you can skip the second match and replace it with a COLUMNS formula, which will basically increase by one every time you copy Right

    COLUMNS($A1:A1) = 1
    When you copy it across it turns into
    COLUMNS($A$1:B1) = 2
    Because the $A is a fixed reference
    and so on

    =IF(INDEX('Completed Skills'!$C$3:$I$22,MATCH('Completed Skills'!$A3,Dashboard!$A$4:$A$23,0),COLUMNS($A$1:A1))="Completed","X","")

+ 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: 05-19-2014, 02:01 PM
  2. Replies: 4
    Last Post: 01-13-2014, 04:16 AM
  3. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  4. [SOLVED] VBA for creating Index-Match every 2 rows where INDEX refer to a different column per row
    By bisaya789 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2012, 03:46 AM
  5. Replies: 2
    Last Post: 03-16-2012, 12:03 PM

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