+ Reply to Thread
Results 1 to 2 of 2

X Lookup duplicating return values

  1. #1
    Registered User
    Join Date
    03-11-2021
    Location
    Leeds
    MS-Off Ver
    Office365
    Posts
    1

    X Lookup duplicating return values

    Hi,

    Please could someone help with this problem...

    I have my data tab with, amongst other information, a table containing customer details and the amount they have spent each day.

    I have a summary page and using a LARGE formula I have extracted the top 15 amounts spent on a particular day (date is fixed in cell B1).

    I now need to match the amount spent, and the day, to return the customer account number in column A of the summary page.

    The problem is when I have two or three customers that have spent the same amount on the same day it only returns the first customer account number that matches the criteria.

    When i have duplicate amounts spent I want to return each customer that has spent that amount.

    In Summary:

    Data tab -
    Column D is customer account number
    Column J is amount spent
    Column X is the date

    Summary tab -

    B1 is the date
    D5:D19 returns highest 15 values from particular date in descending order.
    A5:A19 i want to match these values with the customer number.

    So far I have tried x lookup with a helper column in column A of Data sheet with no success but now trying V Lookup.

    My formula in A5 is =XLOOKUP(D5&$B$1,'Data'!K:K&'Data'!X:X,'Data'!D:D)

    Customer Number Spent
    DC1591 £12,015.00
    SH1443 £9,000.00
    JP1628 £3,496.88
    BH1659 £2,800.01
    TM1509 £1,680.01
    SR1536 £1,117.33
    DL1325 £1,000.00
    CT1660 £350.00
    CT1660 £350.00
    CB1620 £277.41
    VW1603 £200.00
    PH1564 £185.00
    AV1615 £100.00
    AV1615 £100.00
    OR1053 £91.00

    In this example CT1660 is duplicated as is AV1615.

    Many thanks,

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,548

    Re: X Lookup duplicating return values

    Hello Robmarley and Welcome to Excel Forum.
    Please utilize the instructions in the banner at the top of the page to upload an .xlsx file.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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: 1
    Last Post: 05-12-2020, 08:42 AM
  2. Replies: 3
    Last Post: 03-10-2017, 07:56 PM
  3. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  4. lookup values in table, return sum of values from first column
    By owenkam in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-10-2010, 05:20 AM
  5. Replies: 3
    Last Post: 10-23-2008, 04:01 PM
  6. [SOLVED] Lookup without duplicating
    By Dennis S. in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-10-2005, 11:50 AM
  7. Replies: 1
    Last Post: 01-19-2005, 09:06 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