Index match with filters

[ChemistryBMS]

Hi,

I am looking to create an searching system built on values with a > or < operator with an archive position.

I have uploaded (correctly this time) a condensed template of how the one I am working with looks. The desired actions are in purple on tab 'Finder'.

If it is not possible in this format, I am wondering whether a pivot table is best?

Appreciate any assistance with this!

Thanks, ChemistryBMS

[AliGW]

No attachment ...

[ChemistryBMS]

Apologies Ali - attachment now with post!

[AliGW]

Can you tell me all operators you are likely to use?

[ChemistryBMS]

We would only be using the greater or less than operators, i.e., if we wanted to search for all calciums >2.6 then it would return the 2 sample IDs listed.

Similarly, if we wanted to set it to [ALBUMIN | < | 29] it should 3 sample IDs.

[AliGW]

Try this in E2:

=CHOOSECOLS(FILTER(INDIRECT(A2&"!A2:L6"),IF(B2=">",INDIRECT(A2&"!I2:I6")>C2,INDIRECT(A2&"!I2:I6")<C2)),5,9,11)

[AliGW]

Please see post #6.

[ChemistryBMS]

Hi Ali,

Thanks for your prompt response.

I should have made the title clearer with the restrictions my workplace MS systems are, excel 2013 and therefore unable to use the FILTER function.

Using it on my personal laptop the formula does work as expected!

[AliGW]

Your profile said 365 - have you changed it?

Your title was misleading, too.

Over to someone welse - I'm watching the tennis!

[ChemistryBMS]

I haven't changed my MS version so unsure why that shows 365?

I've attached a snapshot of a similar worksheet with less criteria.

Thanks for your help AliGW.

[AliGW]

I produced a 365 solution because that’s what I thought your profile said. I must have been mistaken. I’m off the laptop now.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

[ChemistryBMS]

Hi AliGW,

I have posted a similar query here: https://www.mrexcel.com/board/thread...teria.1240730/

From my misleading title, I believed posting with a revised title and more description may have helped other users to understand my query better. Would you like me to remove the post or is it ok to remain?

Thanks, ChemistryBMS

[AliGW]

You can't remove this thread and you don't need to, but you can improve the title of the opening post to attract more help.

116 views and at least 9 downloads of the original workbook and only I have responded so far - hopefully someone else with Excel 2013 will soon.

If you get a resolution on Mr Excel, please share it here.

[ChemistryBMS]

Hi AliGW,

Fluff from MrExcel has provided a solution:

Cell Formulas
Range Formula
E2:E20 E2 =IFERROR(INDEX(INDIRECT("'"&$A$2&"'!E:E"),AGGREGATE(15,6,ROW(INDIRECT("'"&$A$2&"'!I2:I100"))/(IF($B$2="<",INDIRECT("'"&$A$2&"'!I2:I100")<$C$2,INDIRECT("'"&$A$2&"'!I2:I100")>$C$2)),ROWS(E$2:E2))),"")

F2:F20 F2 =IFERROR(INDEX(INDIRECT("'"&$A$2&"'!I:I"),AGGREGATE(15,6,ROW(INDIRECT("'"&$A$2&"'!I2:I100"))/(IF($B$2="<",INDIRECT("'"&$A$2&"'!I2:I100")<$C$2,INDIRECT("'"&$A$2&"'!I2:I100")>$C$2)),ROWS(E$2:E2))),"")

G2:G20 G2 =IF(E2="","",INDEX('Archive position'!$I$2:$I$100,MATCH(E2,'Archive position'!$E$2:$E$100,0)))

[AliGW]

He’s on here as well.

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

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[wk9128]

worksheet or tab name: Finder
pls try this formula A

Cell E2 formula , Drag down

=IFERROR(INDEX(INDIRECT("'"&$A$2&"'!E:K"),AGGREGATE(15,6,ROW($2:$100)/(IF($B$2=">",INDIRECT("'"&$A$2&"'!I2:I100")>$C$2,INDIRECT("'"&$A$2&"'!I2:I100")<$C$2)),ROW(1:1)),{1,5,7}),"")

pls try this formula B , Drag down and across

=IFERROR(INDEX(INDIRECT("'"&$A$2&"'!E:K"),AGGREGATE(15,6,ROW($2:$100)/(IF($B$2=">",INDIRECT("'"&$A$2&"'!I2:I100")>$C$2,INDIRECT("'"&$A$2&"'!I2:I100")<$C$2)),ROW(1:1)),CHOOSE(COLUMN(A$1),1,5,7)),"")