+ Reply to Thread
Results 1 to 10 of 10

Help returning a name only when it matches 2 criteria

  1. #1
    Registered User
    Join Date
    08-08-2017
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    40

    Help returning a name only when it matches 2 criteria

    Hey,

    I've tried index match, index match match (which seems like it should work but I may be using it wrong) vlookup and other formulas I've found when I've been searching for an answer. If I was returning a number it would be a simple sumifs formula, unfortunately letters are proving difficult.

    Basically I have this data

    sheet 1
    Column A Column B Column C Column D Column E
    Date Vendor Amount Employee Code
    8/2/17 Amazon $100 Joe ABC

    sheet 2
    Column A Column B Column C
    Date Amount Code
    8/2/17 $100 (cell C2) This is where I need the code (sheet 1 cell E2) to automatically enter when the dates and amounts match from sheet 1 in sheet 2

    I can change the code to a number and use sumifs to return the number to a hidden cell (say sheet 2 D2) but then i do not know how to have C2 read the number from D2 and convert that to a word. i.e. 1=ABC 2=DEF and so on in the same cell.

    Hopefully I've explained this simply and clearly

    Thanks in advance for any help

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Help returning a name only when it matches 2 criteria

    I partly understand, but can you add a sample workbook with the sample data and what you expect.

    Based on your description, this should work in Sheet2 C2

    =INDEX(Sheet1!$E$2:$E$10,MATCH(A2&B2,Sheet1!$A$2:$A$10&Sheet1!$C$2:$C$10,0))

    IMPORTANT
    This is an array formula
    Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
    If entered correctly, the formula will be enclosed in {brackets}
    Do not enter the {brackets} manually
    HTH
    Regards, Jeff

  3. #3
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Help returning a name only when it matches 2 criteria

    Hi Ksun23, welcome to Excel Forum! A small sample workbook (NOT a picture) would really help us to solve your problem quickly and accurately.
    Remove any sensitive or extraneous info, just provide some “realistic” data to test w examples of any variations the code or formula must address.
    Also, PLEASE simulate some results to demonstrate what you want.

    To attach a workbook:
    Click Edit Post (or just start a new reply.)
    Click Go Advanced
    Scroll down to Manage Attachments and click.
    Now just Browse for your file, then click Upload. Simple!

  4. #4
    Registered User
    Join Date
    08-08-2017
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    40

    Re: Help returning a name only when it matches 2 criteria

    Thanks for the replies,

    I had previously tried an index match like the one suggested, however I did not use ctrl shift enter which is probably why it didn't work.

    I've uploaded a sample book. I've entered the formula suggested by Jeffrey, as you'll see it works in 2 cells but not others. Let me know if you need any more information, thanks for your time, much appreciated.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help returning a name only when it matches 2 criteria

    One small change to jeffreybrown's formula should make it work.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-08-2017
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    40

    Re: Help returning a name only when it matches 2 criteria

    It certainly did, thanks so much all 3 of you for sharing your knowledge. This will save me a lot of data entry time and spot missing transactions.

    If I could further this a bit, an unlikely problem I've thought of is if there's a transaction with the same date and amount but a different code. Is there a way to return both codes? I've updated the test book to show this potential issue.

    I've also added 2 tabs, is it possible to have the formula in sheet 2 search all 3 tabs? I did attempt it in sheet 2 cell C4 but it didn't work. Is it because my sheet numbers are out of order? 1,3,4,2 rather than 1,2,3,4

    Thanks again
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help returning a name only when it matches 2 criteria

    Why multiple sheets ? the more sheets, the greater the complexity.

    What differentiates Sheets 1, 3,and 4?
    Last edited by JohnTopley; 08-09-2017 at 01:46 AM.

  8. #8
    Registered User
    Join Date
    08-08-2017
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    40
    JohnTopley,

    Each sheet is a different business with it's own transactions. Some of the transactions are with a shared credit card account (sheet 2). Sheets 1, 3 & 4 the data is entered throughout the month, then sheet 2 is re-entered at the end of the month. I'm trying to avoid coding the transactions twice.

    It will work great as is thanks to you guys. Sometimes though sheet 1 will have entries on sheet 3 or 4, or sheet 3 on sheet 4 etc. So when those are reported on sheet 2, they won't be found by the current formula. I can manually enter those if it's going to make it too complex for a formula, it's not a lot of transactions.
    Last edited by jeffreybrown; 08-09-2017 at 07:37 AM. Reason: Removed full quote!

  9. #9
    Registered User
    Join Date
    08-08-2017
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    40
    Thinking about it now though I can just enter the formula into adjacent columns on sheet 2, one column per business. Each column reporting back from it's own corresponding sheet. That should work.

    Thanks for asking the question, problem solved I think.
    Last edited by jeffreybrown; 08-09-2017 at 07:38 AM. Reason: Removed full quote!

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Help returning a name only when it matches 2 criteria

    Glad you have found a working solution.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. search 2 criteria and if it matches give me the number of matches
    By sxch12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-14-2017, 03:16 PM
  2. Index and Match Returning N/A's when there are matches
    By rcdavis28 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-17-2016, 06:33 PM
  3. Returning all that matches criteria across more cells
    By Andrew87.. in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2016, 03:37 PM
  4. Replies: 11
    Last Post: 07-05-2012, 08:03 AM
  5. Returning a number from multiple matches possible
    By Jeffatoffice in forum Excel General
    Replies: 3
    Last Post: 08-03-2011, 04:53 PM
  6. Returning Multiple Matches from a range
    By DentonHTHS in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-27-2008, 12:04 AM
  7. Returning all matches
    By Krussadams in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2006, 03:28 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