# Creating a Dashboard with Vlookup (or Match/Index)

1. ## 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!

2. ## 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"

3. ## 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. ## 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","")

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

#### 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