+ Reply to Thread
Results 1 to 9 of 9

Match Cell date from Column and return value of third column's Cell

  1. #1
    Registered User
    Join Date
    08-03-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    6

    Match Cell date from Column and return value of third column's Cell

    I have one worksheet with two tabs. In one tab, there is customer item information: a column with a list of items, a column with the date they want the item and a column to show how many they want on that date ("Order Report" Check Column C for match to cell in "Calendar" K10, then input "Order Report" Description & Min Qty). In the other tab is a rough calendar.
    The goal is to show the customer item and qty on the date they want them in the calendar on the second tab ("Calendar" Cell K13 & L13).
    I've been trying to input a formula into the cells of the calendar tab that searches the 1st tab for a date, then returns the item and the qty required into the calendar.
    I've tried VLookup, but I must be doing something wrong. I found a similar question in an old forum, but was unable to view the solution.
    Tab 1.jpgTab 2.jpg

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Match Cell date from Column and return value of third column's Cell

    Please upload a workbook with a small, representative sample of your data along with the desired output of the formula/s (manually entered).

    This will help us much more than screenshots. Also, we should not have to re-type your data.

  3. #3
    Registered User
    Join Date
    08-03-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    6

    Re: Match Cell date from Column and return value of third column's Cell

    Sorry about that - thought I attached the workbook and not the images....

    Example Workbook should now be attached along with the formulas I've tried.
    Example 1.xlsx
    Appreciate any help or direction.
    Attached Files Attached Files

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Match Cell date from Column and return value of third column's Cell

    You did not include the desired results in your sample.
    I would be weary of using merged cells.

    K12 =IFERROR(INDEX('Order Report'!$A$3:$A$34,SMALL(IF('Order Report'!$C$3:$C$34=K$10,ROW('Order Report'!$A$3:$A$34)-(ROW('Order Report'!$A$3)-1)),ROWS($A$1:$A1))),"") Ctrl Shift Enter
    Copied down through K17.

    L12 =IFERROR(1/(1/SUMIFS('Order Report'!$D:$D,'Order Report'!$A:$A,K12,'Order Report'!$C:$C,K$10)),"")
    Copied down through L17.

    You can then highlight K12:L17 and copy and paste to all other days. You will have to change the date range to match what row the dates are in.

    Attached is this done for 10/13 - 10/21.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-03-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    6

    Re: Match Cell date from Column and return value of third column's Cell

    This works - that is great, thank you. However, I was looking to get Item descriptions in the calendar versus the item #'s (results from Column B vs Column A).

    Not being ungrateful (because I am grateful- I really am!) but I tried to change the result to show the item description in the calendar instead of the item # and I made it all wonky. I figure it is because I do not fully understand the formula you plugged in to drive the results. Would there happen to be an easier formula than the one? Something like an If Then?
    Last edited by Tieddyekid; 10-16-2017 at 11:45 AM. Reason: wording

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Match Cell date from Column and return value of third column's Cell

    Simply change A to B in the formulas to get Descriptions (from column B) instead of Item # (from column A).

    That is:

    K12 =IFERROR(INDEX('Order Report'!$B$3:$B$34,SMALL(IF('Order Report'!$C$3:$C$34=K$10,ROW('Order Report'!$B$3:$B$34)-(ROW('Order Report'!$B$3)-1)),ROWS($A$1:$A1))),"") Ctrl Shift Enter

    L12 =IFERROR(1/(1/SUMIFS('Order Report'!$D:$D,'Order Report'!$B:$B,K12,'Order Report'!$C:$C,K$10)),"")
    Last edited by 63falcondude; 10-16-2017 at 11:55 AM. Reason: Included formulas

  7. #7
    Registered User
    Join Date
    08-03-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    6

    Re: Match Cell date from Column and return value of third column's Cell

    Thank you falcondude, the formula change worked for the description, but did not pick it up for the qty:
    formula.jpg

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Match Cell date from Column and return value of third column's Cell

    You have to confirm the "Item" formula in K12 with Ctrl Shift Enter instead of just Enter.

    After doing this correctly, Excel will put {curly brackets} around the formula.

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Match Cell date from Column and return value of third column's Cell

    Attached is the workbook from post #3 with the adjusted formulas in place.
    Attached Files Attached Files

+ 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. [SOLVED] If a cell in a column equals today's date return the value of another cell adjacent to it
    By DeanExcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2021, 01:38 AM
  2. Match Cell Substrings to Column and Return Adjacent Cell
    By ashb444 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-06-2015, 11:24 AM
  3. [SOLVED] Return blank cell for formula is there's a match in a column?
    By djarcadian in forum Excel General
    Replies: 3
    Last Post: 02-18-2015, 10:54 AM
  4. Replies: 8
    Last Post: 06-25-2014, 05:43 AM
  5. [SOLVED] Match cell value and return another column value
    By thinkspac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-21-2012, 01:15 PM
  6. Replies: 6
    Last Post: 09-18-2012, 10:43 AM
  7. Replies: 1
    Last Post: 08-02-2012, 11:39 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