# Multiple column match, and seek multiple output values from one column.

Please see the attached ods file with two sheets for the problem.

The problem is, when a name is selected in dropdown at B17 : C:\fakepath\ANLYS.ods.

Scenario 1: To have all the corresponding STRIKE_PR of that particular name filled up in the column D beginning at D17 after matching OPTSTK, CE, 30-May-2019.

Scenario 2: To have only STRIKE_PR of top three highest OPEN_INT corresponding to that particular name. filled up in the column L beginning at L17 after matching OPTSTK, CE, 30-May-2019. (OPEN_INT are in column H in yday sheet)

As you can see, I tried with INDEX and MATCH function, but it is showing only the first matched STRIKE_PR ignoring the rest. Perhaps it requires a different combination of functions.

I hope someone can help / guide me. thanks

edit retag close merge delete

On a first glance it looks as if you can assure that the matches run over a contiguous sequence of rows.
If so, you need to explicitly state this fact because it can simplify the solution very much.

( 2019-06-03 18:53:25 +0200 )edit

Your "Scenario 2" ignores the fact that there is no assurance about "the top three" being unambiguous.
To the extreme: 100 items having the same value of STRIKE_PR would be 100 items on rank 1 when looking for "the top three".

( 2019-06-03 22:15:56 +0200 )edit

@Lupp, 100 items may have same STRIKE_PR but SYMBOL would be different. That said, if you have a simpler solution please do let me / us know. The solution provided by @SM_Riga works and so I am marking this question as answered, but because of the large number of calculations involved, it is causing heavy load on CPU and freezing for a while. thank you.

( 2019-06-06 09:51:44 +0200 )edit

What I sketched may or may not be simpler. I didn't study the already given solution, and in fact I won't. This is obvivusly a database task, and any solution by spreadsheets will be a crook and probably not reliable enough or unsafe concerning data protection / security / safety, or...
However I sketched a kind of solution originally to demonstrate in what way the probable assurance mentioned in my first comment might simplify the task or, at least allow for a more efficient solution. I did not post that example because I didn't get and answer concerning the assurance.
Since you asked for it explicitly I will now have to open a next-to-empty answer to be able to attach the demo.

( 2019-06-06 12:52:41 +0200 )edit

Sort by » oldest newest most voted

Hello, @Sai

Please also refer to this topic

---Edit 2019-05-31 ----

Another sample file for Advanced Filter + Pivot and formula for Top-3 results.

more

thanks @SM_Riga for your suggested solution for Scenario-1. While your solution for Scenario-1 would be helpful, I am unable to reproduce the same result in my analysis spreadsheet. The sample file I uploaded is a shortened version of my analysis spreadsheet, so that it is within upload file size limit here at this website. Original raw data file contains about 50k rows and 15 columns. Can you please explain how did you do it, in very simple language, for me to replicate the same in my file.

As regards filters, yes, know them. But the challenge is, I have to do further analysis in this spreadsheet after fetching relevant data into other columns (not visible in the sample file), which may not be possible with filters.

( 2019-05-30 12:47:47 +0200 )edit

( 2019-05-31 00:40:33 +0200 )edit

Anyway, if you decide to stick to filtering by formulas, I can try to explain step-by-step how sample formula works.

( 2019-05-31 00:47:08 +0200 )edit

Hi @SM_Riga, thanks for providing new ideas for analyzing sample data both with Advanced Filter + Pivot and Formula based methods. (1) Have you observed that are the values in Pivot table not updating, based on AdvancedFilter criteria? while those based on formula are updating! (2) These formulae are new to me. What does the criteria >0, >100, >0 mean / do ?

The reason I am not talking about Libreoffice-base is because of total ignorance about it. Although I am interested in it, if I have to do it through base, I have to start from basics of base. I get raw data file daily in csv format & just read online that csv files cannot be automatically imported into base. I also get few other files in csv format daily, which are linked to a spreadsheet & respective calculations/analysis done automatically. Now after your suggestion, even as I may learn ...(more)

( 2019-05-31 16:44:53 +0200 )edit

Further, I think array formula was used for the Scenario-1, but as already mentioned before, I am not able to replicate it in my own spreadsheet despite repeated attempts (CSE done). Is there anything else that need to be done to get the array formula working for Scenario-1 ? Could you please share the step by step procedure for the same. thanks

( 2019-05-31 16:49:07 +0200 )edit

(1) Have you observed that are the values in Pivot table not updating, based on AdvancedFilter criteria?

Yes, of course. Right click on Pivot Table, then click Refresh The idea is to demonstrate different methods/approaches.

(2) These formulae are new to me. What does the criteria >0, >100, >0 mean / do ?

Just a sample to show that you can use comparison operators in the Filter Criteria table also.

Have you noticed that I used Named Ranges in formulas to make it easier to understand what cell range formula refers to? Do you enter =IF((INSTRUMENT=$D$11)*(OPTION_TYP=$D$13)*(EXPIRY_DT=$D$14)*(SYMBOL=$B$17);1;0) as an array formula?

( 2019-05-31 22:06:31 +0200 )edit

Yes, just seen the defined ranges. I didn't notice this earlier. What is __Anonymous_Sheet_DB_0, __Anonymous_Sheet_DB_1? & Print Area ? & yes, array formula was same as you mentioned.

( 2019-06-01 17:50:03 +0200 )edit

What is __Anonymous_Sheet_DB_0, __Anonymous_Sheet_DB_1? & Print Area ?

These are not created/defined in my sample spreadsheet, so possibly the result of some of your actions. Did you manage to get the formula work in your original spreadsheet after adjusting Named Ranges references?

( 2019-06-02 16:27:41 +0200 )edit

Thanks for guiding me, I downloaded the file with your worked-out solution afresh and followed your procedure, initially to get the results in a exact copy of the sample file uploaded here (not yet in my main spreadsheet, the data / variables to consider in main spreadsheet is a little more). It worked! One small doubt. In the yday sheet there are some workings in columns I, J and K. Was it some side-calculation you did and forgot to delete or are they relevant ? I didn't consider them in my workbook. I tried for a dropdown of the EXPIRY_DT in D14 with the various expiry dates (using data validity option). But that doesn't seem to work even if I choose cell format as date.

( 2019-06-03 17:42:23 +0200 )edit

thank you @SM_Riga. With slight tinkering, I could get the D14 dropdown of EXPIRY_DT working.

( 2019-06-03 17:48:43 +0200 )edit

(Only in puruit of the recent comments on the question. Not claiming anything!)
announced attachment was not accepted due to file size.