Multiple COLUMN match and seek Output Value

[Sai]

Dear all:

I need to reformat raw data of over 45000 rows. It is basically a stock market data file. File attached FNO1.ods. I could attach only part of the file (some rows deleted) as file upload size is restricted.
I tried with VLOOKUP, but it is giving me just the first match for “SYMBOL” without an option to match the “EXPIRY_DATE” and “FUTSTK” columns. I don’t know how to accomplish that. For the table I am preparing I need “FUTSTK”, “SYMBOL” columns to match “EXPIRTY_DATE” value from among several other heads under these columns to choose from and output value has to be corresponding figure in the “CLOSE” column.

The table format I am trying is just beside the raw data (in the same sheet).

I hope somebody can help / guide me.
thanks

(Slightly edited by @Lupp to get the link in the right place.)

[Lupp]

I neither know the jargon nor do I understand the use case at all.

However:
To get matches under multiple conditions you can combine the single matching conditions using the multiplication as an arithmetic surrogate for the logical AND (which cannot be used “per row”, but only “by total”), and then look for the result (or TRUE()). (There are alternatives.)
For any match, if successful at all, you only get one (the first in this case) index position. To assure unambiguous conditions is your obligation.
See this reworked version of your example.

[Sai]

many thanks to you, Lupp. You made it look simple. I was literally scratching my head for several days now, how to accomplish this. I copied the function provided by you for the entire file. It worked! I am bit slow in understanding the function used especially the arithmetic surrogate part of it. I will try to figure it out. thanks once again.

[Lupp]

Boolean (logical) values are internally treated the same way as any numerical values storing them as a 1 in case of TRUE and a 0 in case of FALSE. This allows to carry out logical operations as numeric ones - or in combination with comparisons:
a AND b equals a*b, a OR b is returned by (a+b)>0 …
You need to be careful, however, because function parameters expecting boolean values interptret any number different from 0 as a TRUE, and so does formatting of cells as Boolean. The conversion =TEXT(0.3;"Boolean") returns TRUE and so does =OR(-1/4;+1/4). This may result in visually undetected errors.