+ Reply to Thread
Results 1 to 7 of 7

If statement to get a value from a range if a specific text criteria is met

  1. 06-21-2017, 12:59 PM #1
    sgetraer
    sgetraer is offline
    Registered User
    Join Date
    07-30-2014
    Location
    London, England
    MS-Off Ver
    Office for MAC
    Posts
    25

    If statement to get a value from a range if a specific text criteria is met

    I m trying to get values from a cell which is in a pivot table in a different tab if the text string is the same... Let me try to explain this way
    In a summary tab, I want to get data in monthly tabs that have pivot tables if the item is an exact match... In other words:

    IF the text in Cell A1 (in the summary tab) = text in a range of cells in a different tab (in a pivot table)
    Then enter the value that is in the column three columns to the right within that pivot table in cell B1 in the summary tab

    Hope you can understand and
    Thanks in advance

    Steve
    Last edited by sgetraer; 06-21-2017 at 02:15 PM. Reason: feed back from respondor
    Reply With QuoteRegister To Reply
  2. 06-21-2017, 01:05 PM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: If statement to get a value from a range if a specific text criteria is met

    Hi Steve. Roughly speaking, your solution will take this form:
    =INDEX(ReturnRange,MATCH(LookUpValue,LookUpRange,0))

    If you want a more specific answer, please attach a small sample workbook (NOT a picture) to give us the details.
    Remove any sensitive or extraneous info, just show us what cells contain your data and formulae, what results you want, and where you want them.

    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!
    Last edited by leelnich; 06-21-2017 at 01:15 PM.
    Reply With QuoteRegister To Reply
  3. 06-21-2017, 02:22 PM #3
    sgetraer
    sgetraer is offline
    Registered User
    Join Date
    07-30-2014
    Location
    London, England
    MS-Off Ver
    Office for MAC
    Posts
    25

    Re: If statement to get a value from a range if a specific text criteria is met

    Thanks for the feedback... I have set an example worksheet up... The original worksheet summary listings have as many as 1300 rows and I will be doing this for 17 months, each month will have a subset of the universe in the summary. Once I have the formula, I thought I would be able to copy and paste with some small modifications for other months. I have indicated in red the information that I think you asked for. I really appreciate the help...Steve
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 06-21-2017, 02:48 PM #4
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: If statement to get a value from a range if a specific text criteria is met

    Ok, paste this in Summary!C2 and down:
    =IFERROR(INDEX('April ''17'!$G$3:$G$19,MATCH(Summary!A2,'April ''17'!$B$3:$B$19,0)),"")

    Be very careful when employing look-up values in you worksheets. They MUST match EXACTLY. Watch out for leading/trailing spaces.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Attached Files Attached Files
    Last edited by leelnich; 06-21-2017 at 02:51 PM.
    Reply With QuoteRegister To Reply
  5. 06-22-2017, 11:15 AM #5
    sgetraer
    sgetraer is offline
    Registered User
    Join Date
    07-30-2014
    Location
    London, England
    MS-Off Ver
    Office for MAC
    Posts
    25

    Re: If statement to get a value from a range if a specific text criteria is met

    That was great, but I am having one last problem... I have found that the list may have some spelling changes in it from month to month.... However, the leading numbers are the same... Is there a way to get the formula to work just looking at the initial string which is in the form . 1234 The remainder will have - abcd. The existing formula you gave me is: =IFERROR(INDEX('Dec. ''16'!$P$3:$P$1006,MATCH(Summary!$A5,'Dec. ''16'!$H$3:$H$1006,0)),"")

    Again, really appreciate the help... and education

    Steve
    Reply With QuoteRegister To Reply
  6. 06-22-2017, 01:36 PM #6
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: If statement to get a value from a range if a specific text criteria is met

    Hi Steve- Paste this ARRAY FORMULA* on row 5 (or change the $A5 reference) and copy down:
    =IFERROR(INDEX('Dec. ''16'!$P$3:$P$1006,MATCH(LEFT(TRIM(Summary!$A5),4),LEFT(TRIM('Dec. ''16'!$H$3:$H$1006),4),0)),"")

    *Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Reply With QuoteRegister To Reply
  7. 06-26-2017, 03:15 PM #7
    sgetraer
    sgetraer is offline
    Registered User
    Join Date
    07-30-2014
    Location
    London, England
    MS-Off Ver
    Office for MAC
    Posts
    25

    Re: If statement to get a value from a range if a specific text criteria is met

    Thanks so much. Worked perfectly... Now intrigued by using arrays...including the CTRL+SHIFT+ENTER part
    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. Max If statement with multiple criteria based on specific text in a cell Issue
    By Ahldie333 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2015, 04:26 AM
  2. Index & Match with IF statement for specific criteria
    By brad_x81 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-27-2015, 08:15 AM
  3. [SOLVED] VBA to Find Specific Text, then Copy range to next open cell under specific text
    By Remotruker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2015, 10:59 AM
  4. Most Frequent text within a range under specific criteria
    By afronight_76 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2014, 12:16 PM
  5. [SOLVED] IF Statement which includes an outcome only if specific criteria are met
    By lynnepooh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2013, 03:03 PM
  6. Find specific text in cell that matches range, then display specific text
    By Knawl in forum Excel General
    Replies: 2
    Last Post: 08-05-2013, 07:32 PM
  7. [SOLVED] Extract Specific Data from range dependant on text criteria
    By Grimace in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-09-2013, 08:43 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1