+ Reply to Thread
Results 1 to 4 of 4

Index/Match Not Pulling Formula-Created Numbers? Please Help!

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    75

    Question Index/Match Not Pulling Formula-Created Numbers? Please Help!

    I am having trouble pulling data from one spreadsheet into a table on another and would appreciate some help! In the attached, I have two worksheets: "Product SKUs" and "SKU List". I need to identify how many times a product SKU registered with an online attribute code at time of sale - columns A & B of "SKU List" pull all the occurrences over the date range and I need to report as follows: "SKU 123 received 5 different attribute codes over the date range". I do not need to know which codes SKU 123 received for this metric - I do, however, need to know the unique count. So if SKU 123 registered 12 times and used 5 different codes, I need to report 5, not 12. If SKU 234 also contained one of the 5 codes SKU 123 shows, that is fine - unique attributes per SKU is what I'm after.

    In "SKU List", I tried to concatenate A & B's raw data of SKUs to attributes, sum the results in a pivot, and re-separate the concatenation. I then tried to use an index/match formula to pull the codes number on the "Product SKUs" sheet but this number is consistently returning "#N/A" (not all SKUs will have results but some/many will [though this sheet has been hella trimmed down for a sample]). I suspect this is because the SKU number pulled on the "SKU List" sheet is formulaic - if I directly type the SKU # over the formula-produced number on "SKU List" (col. M), the number pulls using index/match, but I do not have time to manually re-type each number (and have not found a general/number/text formatting option that seems to work).

    Can you please help me understand what I need to do? All suggestions appreciated! Thanks in advance!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Index/Match Not Pulling Formula-Created Numbers? Please Help!

    Hi,

    Addressing your INDEX/MATCH formula in Product SKU sheet in B2 down, you have TEXT values in SKU List Column J which is then copied over to Column L, but your Product SKU Column A are Real Numbers.

    So they Don't match up, that's why you're getting #N/A errors for the entire Product SKU Column B.

    If the SKU numbers Never Starts with a 0 (zero), then convert SKU List Column J and/or Column L to Real Numbers, otherwise, Convert Product SKU Column A to Text.
    Then your Product SKU Column B formula should work.

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

    Re: Index/Match Not Pulling Formula-Created Numbers? Please Help!

    Hi eNinja,

    I think you need to do an advanced filter to remove duplicates and then do a Pivot Table using Count. See the attached where all the work is done on the second sheet.

    Adv Filter Unique then Pivot Count.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    75

    Re: Index/Match Not Pulling Formula-Created Numbers? Please Help!

    Thank you both for your feedback!

    jtakw, formatting my numbers as either all text or all numbers did not resolve the problem - even though I could apply the format to those columns/cells, the cells were still formula-derived and did not develop other properties of numbers (i.e. ability to increase or decrease decimal places), even after changing the source columns to a uniform format. Thanks for trying to help!

    MarvinP, I had only thought of filtering with a pivot - your suggestion, not requiring an additional concatenation and de-concatenation, actually works! Thanks a lot - was able to get my numbers! Problem solved!

+ 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. Index Match Formula Not Pulling Correct Results
    By derivative2016 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-23-2016, 12:54 PM
  2. Index Match Formula Not Pulling Correct Results
    By derivative2016 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-23-2016, 10:33 AM
  3. Index match formula is not pulling the correct data
    By thomashasler in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-20-2015, 03:14 PM
  4. [SOLVED] MATCH/INDEX Formula Help - Pulling Data
    By mlbdc2012 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-03-2015, 01:43 AM
  5. Index-Match Formula is pulling incorrect value
    By wghalim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2014, 08:16 AM
  6. [SOLVED] Help-> Index+Match formula pulling wrong values
    By sashafierce in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-29-2013, 07:13 PM
  7. Index/Match formula not pulling through desired results
    By Climaxgp in forum Excel General
    Replies: 0
    Last Post: 08-02-2010, 08:14 AM

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