+ Reply to Thread
Results 1 to 11 of 11

Formula to get an value from multiple criterias

  1. 03-18-2017, 09:35 AM #1
    Vikramchetan
    Vikramchetan is offline
    Registered User
    Join Date
    03-18-2017
    Location
    UAE
    MS-Off Ver
    2013
    Posts
    17

    Exclamation Formula to get an value from multiple criterias

    Dear All,

    I need assistance on my below query.!!

    I have two sheets in excel lets say sheet1 & sheet2.

    I have 7 columns on both the sheets with same column names (like below)

    Product, Product grade, Reference no, Weight, Type, Source, Destination

    I'm getting the data from SAP and pasting it in sheet 2. Now I need to input an formula in sheet 1 to get the values from sheet 2 columns.

    Lets say to the get product from sheet 2 in sheet 1 by referring to Destination (which I have around 5 destination like Singapore, china, Malaysia etc..) and reference number like (10, 11, 12 etc..).

    here I need to get the product that goes to china with reference number 10 which should not include those products going to other destinations with same reference number 10.

    On the other hand I need to get the values for all the columns which has destination e.g. china and reference number e.g. 10 but not other destinations with same reference number.

    In the same way I need to get the other columns product grade, reference number, weight, type, source, destination from sheet 2 in sheet 1 automatically after I input the data in sheet 2.

    I have attached an sample excel file for reference.

    Kindly help me please!!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-18-2017, 09:36 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,197

    Re: Formula to get an value from multiple criterias

    Welcome to the forum!

    It will be easier to help if you include your EXPECTED OUTCOMES on sheet 1 instead of a blank table.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 03-18-2017, 10:00 AM #3
    Vikramchetan
    Vikramchetan is offline
    Registered User
    Join Date
    03-18-2017
    Location
    UAE
    MS-Off Ver
    2013
    Posts
    17

    Re: Formula to get an value from multiple criterias

    Quote Originally Posted by AliGW View Post
    Welcome to the forum!

    It will be easier to help if you include your EXPECTED OUTCOMES on sheet 1 instead of a blank table.

    Mam, thanks for your prompt reply.

    I have attached the updated file with desired outcome. Kindly refer to it and advise

    Thanks for your assistance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 03-18-2017, 08:33 PM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,817

    Re: Formula to get an value from multiple criterias

    What about setting up a table populated with the simple formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    You can then set the destination filter as desired.
    Let us know if you have any questions.
    Attached Files Attached Files
    Last edited by JeteMc; 03-18-2017 at 08:35 PM. Reason: Added .xlsx file
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  5. 03-18-2017, 10:31 PM #5
    Vikramchetan
    Vikramchetan is offline
    Registered User
    Join Date
    03-18-2017
    Location
    UAE
    MS-Off Ver
    2013
    Posts
    17

    Thumbs up Re: Formula to get an value from multiple criterias

    Quote Originally Posted by JeteMc View Post
    What about setting up a table populated with the simple formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    You can then set the destination filter as desired.
    Let us know if you have any questions.
    Thanks for your reply.

    But I don't want to use filter since I don't want those 3 destination to be appeared in sheet 1.

    Is there any other way?

    Thanks once again.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-18-2017, 11:07 PM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,817

    Re: Formula to get an value from multiple criterias

    This proposed solution employs a helper column on sheet 2.
    The helper column is populated by the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    J2:L2 refers to a range that has the countries to be omitted which can be changed if needed.
    The formula that populates the table on sheet 1 is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 03-19-2017, 07:47 AM #7
    Vikramchetan
    Vikramchetan is offline
    Registered User
    Join Date
    03-18-2017
    Location
    UAE
    MS-Off Ver
    2013
    Posts
    17

    Re: Formula to get an value from multiple criterias

    Thanks for your assistance.
    Last edited by AliGW; 03-19-2017 at 07:51 AM.
    Reply With QuoteRegister To Reply
  8. 03-19-2017, 07:52 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,197

    Re: Formula to get an value from multiple criterias

    Don't quote whole posts, please - it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    For normal conversational replies, try using the QUICK REPLY box below.

    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 With QuoteRegister To Reply
  9. 03-19-2017, 09:12 AM #9
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,817

    Re: Formula to get an value from multiple criterias

    You're Welcome and thank you for the feedback. As AliGW states, please takae a moment to mark the thread as 'Solved'. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
  10. 03-19-2017, 10:19 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Formula to get an value from multiple criterias

    If you're interested, here's a version WITHOUT a helper column.

    In B3, an array formula, copied across and down:

    =INDEX(Sheet2!B:B,SMALL(IF(ISERROR(MATCH(Sheet2!$G$3:$G$14,{"China","Malaysia","Australia"},0)),ROW(Sheet2!$B$3:$B$14)),ROWS(B$3:B3)))

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  11. 03-19-2017, 10:46 AM #11
    PietBom
    PietBom is offline
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    353

    Re: Formula to get an value from multiple criterias

    I used Advanced Filter using 3 named ranges: dba, Crit1 and Outp1
    To automate it a macro (VBA) was created and linked to button [Run Filter1]
    Attached Files Attached Files
    Kind regards,
    Piet Bom
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to Sumproduct with multiple criterias
    By kunjanee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-17-2016, 01:56 PM
  2. IF formula or multiple criterias
    By Doongchen in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-08-2016, 10:32 PM
  3. [SOLVED] Simplify this formula for summing a row with multiple criterias
    By phatal in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-17-2015, 04:33 AM
  4. Four rule statement with multiple criterias (Formula?, macro?, IDK??)
    By bwalling6 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2013, 04:49 PM
  5. multiple criterias in SUMIF formula
    By aysrun in forum Excel General
    Replies: 8
    Last Post: 07-29-2009, 02:40 AM
  6. making a formula with multiple criterias
    By abrunstad in forum Excel General
    Replies: 4
    Last Post: 03-30-2009, 05:23 PM
  7. Creation formula with multiple criterias
    By s-vadik in forum Excel General
    Replies: 13
    Last Post: 10-24-2008, 02:35 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1