# Serach a range of criteria to ge tthe result

1. ## 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. ## 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:
`Please Login or Register  to view this content.`

or an array formula. i dont know how slow it would take, but try::
Formula:
`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:
`Please Login or Register  to view this content.`

3. ## 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?

4. ## 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. ## Re: Serach a range of criteria to ge tthe result

Originally Posted by Glenn Kennedy
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. ## Re: Serach a range of criteria to ge tthe result

i copied the wrong formula:
Formula:
`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:
`Please Login or Register  to view this content.`

7. ## 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"

8. ## Re: Serach a range of criteria to ge tthe result

Originally Posted by benishiryo
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:
`Please Login or Register  to view this content.`

or an array formula. i dont know how slow it would take, but try::
Formula:
`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:
`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. ## Re: Serach a range of criteria to ge tthe result

Originally Posted by benishiryo
i copied the wrong formula:
Formula:
`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:
`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. ## 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. ## Re: Serach a range of criteria to ge tthe result

Originally Posted by benishiryo
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. ## Re: Serach a range of criteria to ge tthe result

Originally Posted by benishiryo
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

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