+ Reply to Thread
Results 1 to 4 of 4

Pivot Table - Matching the expected value

  1. #1
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    Pivot Table - Matching the expected value

    I have a pivot table problem, and it's hard to provide an example so I'll do my best to explain:

    First formula in my main tab:

    My first formula is working great. I am matching the value found in Column A8 of my main tab to a tab called 'Turntime' with this formula:

    =IF(ISNA(VLOOKUP(A8,Turntime!A:B,2,0)),0,VLOOKUP(A8,Turntime!A:B,2,0))

    This formula will include all possibilities (approximately 100) so that when each value matches, I will return the value found in Column C.

    Second Formula:

    I'm not getting my expected results with this formula:

    I am attempting to match the names from my main tab (about 100 names in Column A) to my Turntime tab in Column E of about 50 names. What am I seeing is that when my main table doesn't match the value found in Turntime, I am not outputting the expected value.

    Example:

    Column A of main tab = CustomerA
    Column E of Turntime Tab = CustomerA
    Result of this match should return the value found in Column F, else a zero.

    This is my current formula:

    =IF(ISNA(VLOOKUP($A8,Turntime!A:F,6,0)),0,VLOOKUP($A8,Turntime!A:F,6,0))


    Main Tab:
    Column A:
    CustomerA
    CustomerB
    ....

    Turntime Tab:
    Column A: Column B: Column C: Column D: Column E: Column F: Column G:
    CustomerA 13 78 CustomerA 10 0

  2. #2
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    Re: Pivot Table - Matching the expected value

    Bump for the Sunday crowd!

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Pivot Table - Matching the expected value

    Hi bdav,

    Check the spelling of "CustomerA" and that there are no leading or trailing spaces (or extra spaces in between) to insure they match EXACTLY. Too often the problems is with extra spaces when the VLookup( , , , False) is used.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Pivot Table - Matching the expected value

    Hi,

    If column E contains the lookup values, then that needs to be the first column in the lookup range thus
    =IF(ISNA(VLOOKUP($A8,Turntime!E:F,2,0)),0,VLOOKUP($A8,Turntime!E:F,2,0))
    which you can probably reduce to simply this
    =IFERROR(VLOOKUP($A8,Turntime!E:F,2,0),0)
    Don
    Please remember to mark your thread 'Solved' when appropriate.

+ 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. Expected table is not in the expected format - ADODB Connection to Read Only Excel file
    By Roshan10043 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2018, 10:54 AM
  2. Pivot Help - Matching / Outputting the expected value
    By bdav1216 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-18-2017, 12:29 PM
  3. [SOLVED] Pivot Table filter is not working as expected
    By tuph in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-20-2015, 09:20 PM
  4. Matching function or Pivot Table
    By lazytkina1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-02-2015, 06:32 PM
  5. matching the column with a value from pivot table
    By mumsys in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-29-2013, 02:29 PM
  6. [SOLVED] data not matching in Pivot table
    By cool.friend in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 06-23-2013, 12:35 PM
  7. [SOLVED] Pivot Table Wizard Error (Expected 52)
    By Guest in forum Excel General
    Replies: 0
    Last Post: 04-28-2006, 05:15 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