Ask Your Question

Multiple COLUMN match and seek Output Value

asked 2019-05-22 16:44:29 +0100

Sai gravatar image

updated 2019-05-22 16:56:57 +0100

Lupp gravatar image

Dear all:

I need to reformat raw data of over 45000 rows. It is basically a stock market data file. File attached C:\fakepath\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.)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-05-22 17:17:20 +0100

Lupp gravatar image

updated 2019-05-22 17:19:22 +0100

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

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.

edit flag offensive delete link more


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.

Sai gravatar imageSai ( 2019-05-22 18:39:09 +0100 )edit

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.

Lupp gravatar imageLupp ( 2019-05-22 19:06:06 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-05-22 16:44:29 +0100

Seen: 235 times

Last updated: May 22 '19