+ Reply to Thread
Results 1 to 12 of 12

Serach a range of criteria to ge tthe result

  1. #1
    Forum Contributor
    Join Date
    03-19-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    101

    Serach a range of criteria to ge tthe result

    Hi

    The attachment has all the details.

    This list of data will actually go for about 30000 lines.

    Hope you can help

    Seven.xlsx

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Serach a range of criteria to ge tthe result

    considering you have 30,000 over rows, you might want to copy this formula down in row 2:
    =IF(L2<>"",L2,G2)

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


    or an array formula. i dont know how slow it would take, but try::
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    if you dont want to add the additional column, this array formula below would run slower. see if it works:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,087

    Re: Serach a range of criteria to ge tthe result

    I'm a bit confused, but it's early a.m.

    Q1on your sheet, rows 21-22. Is "2.2.1: 13 Week Full Outcomes" the starting point for your query (the formulae returning the values in the other 3 cells), or is that what you want that group of cells to return?
    Glenn



  4. #4
    Forum Contributor
    Join Date
    03-19-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    101

    Re: Serach a range of criteria to ge tthe result

    I have tried the last array formula but it come up as #N/A, any suggestions?

    I don't want to add a line to this report, but also ary of the speed and size of the report I am trying to buil.

  5. #5
    Forum Contributor
    Join Date
    03-19-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    101

    Re: Serach a range of criteria to ge tthe result

    Quote Originally Posted by Glenn Kennedy View Post
    I'm a bit confused, but it's early a.m.

    Q1on your sheet, rows 21-22. Is "2.2.1: 13 Week Full Outcomes" the starting point for your query (the formulae returning the values in the other 3 cells), or is that what you want that group of cells to return?
    The Q1 is like step 1, in my head this is the steps the formula would need to take to work.

  6. #6
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Serach a range of criteria to ge tthe result

    i copied the wrong formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and remove the extra spaces you have in column G and L. "CC " has a space & so does "THE ". if it's supposed to have space, try a little adjustment:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,524

    Re: Serach a range of criteria to ge tthe result

    =INDEX($A$2:$S$13,IFERROR(MATCH($A22&$A21&$B21,INDEX(TRIM($A$2:$A$13)&" "&TRIM($L$2:$L$13)&" "&TRIM($N$2:$N$13)&TRIM($Q$2:$Q$13)&TRIM($R$2:$R$13),0),0),MATCH($A22&$A21&$B21,INDEX(TRIM($A$2:$A$13)&" "&TRIM($G$2:$G$13)&" "&TRIM($N$2:$N$13)&TRIM($Q$2:$Q$13)&TRIM($R$2:$R$13),0),0)),19)
    Try this formula in "B18"
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  8. #8
    Forum Contributor
    Join Date
    03-19-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    101

    Re: Serach a range of criteria to ge tthe result

    Quote Originally Posted by benishiryo View Post
    considering you have 30,000 over rows, you might want to copy this formula down in row 2:
    =IF(L2<>"",L2,G2)

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


    or an array formula. i dont know how slow it would take, but try::
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    if you dont want to add the additional column, this array formula below would run slower. see if it works:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I have tried the last array formula but it come up as #N/A, any suggestions?

    I don't want to add a line to this report, but also ary of the speed and size of the report I am trying to buil.

  9. #9
    Forum Contributor
    Join Date
    03-19-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    101

    Re: Serach a range of criteria to ge tthe result

    Quote Originally Posted by benishiryo View Post
    i copied the wrong formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and remove the extra spaces you have in column G and L. "CC " has a space & so does "THE ". if it's supposed to have space, try a little adjustment:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks that works.

    Would it be better for my report size and speed to use the array formula or a formula that is not an array like Siva has added?

  10. #10
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Serach a range of criteria to ge tthe result

    why don't you try it out? i have not tested it exactly the way Siva did it, but to my knowledge, using INDEX to remove the need of pressing CTRL + SHIFT + ENTER actually made speed even slower. here's what i have tested in the past (again, not exactly this scenario)
    http://www.excelforum.com/excel-form...-and-cons.html

  11. #11
    Forum Contributor
    Join Date
    03-19-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    101

    Re: Serach a range of criteria to ge tthe result

    Quote Originally Posted by benishiryo View Post
    why don't you try it out? i have not tested it exactly the way Siva did it, but to my knowledge, using INDEX to remove the need of pressing CTRL + SHIFT + ENTER actually made speed even slower. here's what i have tested in the past (again, not exactly this scenario)
    http://www.excelforum.com/excel-form...-and-cons.html
    Ok I will test it out, Thanks

  12. #12
    Forum Contributor
    Join Date
    03-19-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    101

    Re: Serach a range of criteria to ge tthe result

    Quote Originally Posted by benishiryo View Post
    why don't you try it out? i have not tested it exactly the way Siva did it, but to my knowledge, using INDEX to remove the need of pressing CTRL + SHIFT + ENTER actually made speed even slower. here's what i have tested in the past (again, not exactly this scenario)
    http://www.excelforum.com/excel-form...-and-cons.html
    There is one last thing to finish this formula, if A18 can't be found it currently shows up as #N/A, can you change the formula so that any #N/A shows as a 0

    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. [SOLVED] Pricelist with multiple serach facility
    By makinmomb in forum Excel Formulas & Functions
    Replies: 28
    Last Post: 04-05-2014, 03:25 AM
  2. Countif using VBA to define range, criteria and result cell
    By mantili in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-24-2013, 02:19 PM
  3. Serach for text
    By Rodney in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-24-2006, 08:21 PM
  4. [SOLVED] Labelling bar graph-the bars in X-axis not via tthe legend
    By MikeR-Oz in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-03-2006, 06:30 PM
  5. [SOLVED] serach using a cell value
    By BrianP in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-15-2005, 11:05 AM

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