vlookup on double criteria

Hello there,

I have a table with columns Expiry / Strikes / ... / ... / Price with
hundreds of lines

Basically I need to retrieve the field price corresponding to one line
meeting two criteria of Expiry and Strike (as for exemple Decembre and
55)
Unfortunately a Vlookup would not do it as I would be only to search
for whether December or 55. I would be fine if I had only one search
criteria, if I am right.

Any idea of how should I process would be welcomed!

best regards
Daniel

I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))

daniroy@gmail.com wrote:
>
> Hello there,
>
> I have a table with columns Expiry / Strikes / ... / ... / Price with
> hundreds of lines
>
> Basically I need to retrieve the field price corresponding to one line
> meeting two criteria of Expiry and Strike (as for exemple Decembre and
> 55)
> Unfortunately a Vlookup would not do it as I would be only to search
> for whether December or 55. I would be fine if I had only one search
> criteria, if I am right.
>
> Any idea of how should I process would be welcomed!
>
> best regards
> Daniel

--

Dave Peterson

thank you once again Dave, but maybe should I give more colours, as
currently I am not able to implement your solution ...

On the Sheet called "Implied Dividend", cell(Q,2) is showing 5325 as a
value, lets call it the strike.
On the Sheet called "Implied Dividend", cell(D,5) is showing 21/10/2005
as a date.

Another sheet called "FTSE" display 400 lines, and, lets say 12
columns.
Lines are first sorted by date (16/09/2005, 21/10,2005 ... only one
date per month) and then per strike: 4525,4625,4725...

My goal is to be able to return the value in column E, for example,
corresponding to the price, for the line corresponding to 21/10/2005
for the date and 5325 for the strike ...

And I still have no clue of how to proceed ...

best regards
Daniel

One way ..

In Implied Dividend:
-----------------
Put this in say, E5, and array-enter the formula
(i.e. press CTRL+SHIFT+ENTER):

=INDEX(FTSE!E1:E400,MATCH(1,(FTSE!A1:A400='Implied
Dividend'!D5)*(FTSE!B1:B400='Implied Dividend'!Q2),0))

It's v.similar to what Dave suggested earlier, except perhaps for a missing
"0" in Dave's range: $c$1:$c$100, the formula's array-entering, and the fact
that we drive on the left over here, i.e. the criteria ranges, e.g. ..
(FTSE!A1:A400='Implied Dividend'!D5) .. are preferred on the left side of
the equal sign, rather than on the right <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
<daniroy@gmail.com> wrote in message
news:1126100392.132557.290620@o13g2000cwo.googlegroups.com...
> thank you once again Dave, but maybe should I give more colours, as
> currently I am not able to implement your solution ...
>
> On the Sheet called "Implied Dividend", cell(Q,2) is showing 5325 as a
> value, lets call it the strike.
> On the Sheet called "Implied Dividend", cell(D,5) is showing 21/10/2005
> as a date.
>
> Another sheet called "FTSE" display 400 lines, and, lets say 12
> columns.
> Lines are first sorted by date (16/09/2005, 21/10,2005 ... only one
> date per month) and then per strike: 4525,4625,4725...
>
> My goal is to be able to return the value in column E, for example,
> corresponding to the price, for the line corresponding to 21/10/2005
> for the date and 5325 for the strike ...
>
> And I still have no clue of how to proceed ...
>
> best regards
> Daniel
>

Thanks for the amplification.

I never noticed the missing 0 and forgot the array entered warning.

(So it was pretty much perfekt!)

And you've never seen me drive!

Max wrote:
>
> One way ..
>
> In Implied Dividend:
> -----------------
> Put this in say, E5, and array-enter the formula
> (i.e. press CTRL+SHIFT+ENTER):
>
> =INDEX(FTSE!E1:E400,MATCH(1,(FTSE!A1:A400='Implied
> Dividend'!D5)*(FTSE!B1:B400='Implied Dividend'!Q2),0))
>
> It's v.similar to what Dave suggested earlier, except perhaps for a missing
> "0" in Dave's range: $c$1:$c$100, the formula's array-entering, and the fact
> that we drive on the left over here, i.e. the criteria ranges, e.g. ..
> (FTSE!A1:A400='Implied Dividend'!D5) .. are preferred on the left side of
> the equal sign, rather than on the right <g>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> <daniroy@gmail.com> wrote in message
> news:1126100392.132557.290620@o13g2000cwo.googlegroups.com...
> > thank you once again Dave, but maybe should I give more colours, as
> > currently I am not able to implement your solution ...
> >
> > On the Sheet called "Implied Dividend", cell(Q,2) is showing 5325 as a
> > value, lets call it the strike.
> > On the Sheet called "Implied Dividend", cell(D,5) is showing 21/10/2005
> > as a date.
> >
> > Another sheet called "FTSE" display 400 lines, and, lets say 12
> > columns.
> > Lines are first sorted by date (16/09/2005, 21/10,2005 ... only one
> > date per month) and then per strike: 4525,4625,4725...
> >
> > My goal is to be able to return the value in column E, for example,
> > corresponding to the price, for the line corresponding to 21/10/2005
> > for the date and 5325 for the strike ...
> >
> > And I still have no clue of how to proceed ...
> >
> > best regards
> > Daniel
> >

--

Dave Peterson

It's a pleasure to be able to help out a little, one who has helped
countless others many times over, including this writer !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Absolutely! Sorry I did not come back earlier to thank you, but I had
to leave office early yesterday. Dave contribution is huge indeed, and
thanks a lot to Max too. This group is wonderfull for excel and vba
illeterrate people as me, without any decent IT support!

thanks agains
Daniel

Max, Dave,

Post your replies, I took some time to implement the formula you
suggest.
Up to now, here is what I achieved to setup:
=INDEX(FTSE!$F$10:$K$500,MATCH(Q2,FTSE!$G$10:$G$500,0),6)
this formula is indeed briging me the value I am aiming for, but
unfortunatelly, only for the very first"Expiry"

To make myself more clear, my spreadsheet is build has follow
Column F Column G Column K
Expiry Strike Price
16/09/2005 5225 52
16/09/2005 5325 46
16/09/2005 5425 44
16/09/2005 5525 38
16/09/2005 5625 36
21/10/2005 5225 72
21/10/2005 5325 64
21/10/2005 5425 59
16/09/2005 5525 55
16/09/2005 5625 51

I am thus retrieving 46 according Cell Q2 = 5325 in the formula
=INDEX(FTSE!$F$10:$K$500,MATCH(Q2,FTSE!$G$10:$G$500,0),6)
It is indeed fine if I want to retrieve the Price for Expiry :
Septembre and Strike : 5325
But I still do not understand what should I do to retrieve Prices for
October expiry???

To be honest, I do not really understand the logic behind the
MATCH(1,(FTSE!A1:A400='Implied Dividend'!D5)*(FTSE!B1:B400='Implied
Dividend'!Q2) in Max formula. May be is that linked ?

Best regards, and be sure I appreciate your help !

Daniel

This portion of the formula:

(FTSE!A1:A400='Implied Dividend'!D5)
Will return an array of 400 true/falses (depending if A1:A400 match D5 in
implied dividend)

(FTSE!B1:B400='Implied Dividend'!Q2)
This will return a separate array of 400 trues/falses based on B1:B400 and Q2.

If you put True in A1 and True in B1 and put this formula in C1: =a1*b1
You'll see that True*True = 1
Change A1 or B1 to False (cycle through all 4 combinations) and you'll see that

true*true = 1
true*false = 0
false*true = 0
false*false = 0

So when we multiply those 400 elements of the first array by their corresponding
elements in the second array, you'll end up with an array of 400 1's and 0's.

=match(1,{0,0,1,0,0,1,1,1,0,0....},0)
will return the index into that array that matches 1 (3 in my example).

Then
=index(yoursinglecolumnrange,thatindexnumber)
will return the value when both criteria are true.

A couple of nice things about that syntax (with all the corrections that Max
wrote):

You can add more ranges pretty easily--just include it in the multiplication.

And it doesn't suffer from the same problem that concatenating cells might
suffer:

Col A Col B Col C
aaaa bbb 1
aaa abbb 2

Concatenating A&B would result in a string of "aaaabbb" in both cases which
would cause trouble if you wanted the second line.

You could insert a separator--but you'd have to know your data to make sure you
used a character that wouldn't be used.

=============
Since it looks like the days in your dates could vary, you could use a formula
like to eliminate the day in the date:

=INDEX(Othersheet!$K$1:$K$100,
MATCH(1,(TEXT(A2,"yyyymm")=TEXT(Othersheet!$f$1:$f$100,"yyyymm"))
*(B2=Othersheet!$g$1:$g$100)))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.


daniroy@gmail.com wrote:
>
> Max, Dave,
>
> Post your replies, I took some time to implement the formula you
> suggest.
> Up to now, here is what I achieved to setup:
> =INDEX(FTSE!$F$10:$K$500,MATCH(Q2,FTSE!$G$10:$G$500,0),6)
> this formula is indeed briging me the value I am aiming for, but
> unfortunatelly, only for the very first"Expiry"
>
> To make myself more clear, my spreadsheet is build has follow
> Column F Column G Column K
> Expiry Strike Price
> 16/09/2005 5225 52
> 16/09/2005 5325 46
> 16/09/2005 5425 44
> 16/09/2005 5525 38
> 16/09/2005 5625 36
> 21/10/2005 5225 72
> 21/10/2005 5325 64
> 21/10/2005 5425 59
> 16/09/2005 5525 55
> 16/09/2005 5625 51
>
> I am thus retrieving 46 according Cell Q2 = 5325 in the formula
> =INDEX(FTSE!$F$10:$K$500,MATCH(Q2,FTSE!$G$10:$G$500,0),6)
> It is indeed fine if I want to retrieve the Price for Expiry :
> Septembre and Strike : 5325
> But I still do not understand what should I do to retrieve Prices for
> October expiry???
>
> To be honest, I do not really understand the logic behind the
> MATCH(1,(FTSE!A1:A400='Implied Dividend'!D5)*(FTSE!B1:B400='Implied
> Dividend'!Q2) in Max formula. May be is that linked ?
>
> Best regards, and be sure I appreciate your help !
>
> Daniel

--

Dave Peterson

As a complement to Dave's clear explanations (thanks, Dave !) on the formula
...

Here's a link to a demo file where I've tried to replicate your set-up,
with a suggested implementation:
http://www.savefile.com/files/1003357
File Name vlookup on double criteria_daniroy_gen.xls

Your posted source data is faithfully replicated in sheet: FTSE
data within F10:K19

In sheet: Implied Dividend
-------------------------
Assumed set-up

In Q2 down are the strike prices: 5325,5225, 5625, etc ...

In R1 across are the dates*: Sep-05, Oct-05, Nov-05, etc >>
*1st of month dates, e.g.: 1-Sep-2005, 1-Oct-2005
formatted as: mmm-yy

The formula placed in R2,
and array-entered with CTRL+SHIFT+ENTER:

=INDEX(FTSE!$F$10:$K$500,MATCH(1,(TEXT(FTSE!$F$10:$F$500,"yyyymm")=TEXT(R$1,
"yyyymm"))*(FTSE!$G$10:$G$500=$Q2),0),6)

R2 is then copied across and filled down as required to populate the table

----

And perhaps better with error trapping to return blanks: ""
instead of ugly #N/As for unmatched cases,

In sheet: Implied Dividend (2)
--------------------
Formula placed in R2, array-entered with CTRL+SHIFT+ENTER:

=IF(ISNA(MATCH(1,(TEXT(FTSE!$F$10:$F$500,"yyyymm")=TEXT(R$1,"yyyymm"))*(FTSE
!$G$10:$G$500=$Q2),0)),"",INDEX(FTSE!$F$10:$K$500,MATCH(1,(TEXT(FTSE!$F$10:$
F$500,"yyyymm")=TEXT(R$1,"yyyymm"))*(FTSE!$G$10:$G$500=$Q2),0),6))

R2 copied across and filled down as required as before
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Dave, Max, thank you so much for your incredible explanations! Not only
the solution is working absolutely fine but I am now also able to
understand the logic behind it. Thanks again, Max, for the spreadsheet!

Best regards
Daniel

You're welcome !
Glad to hear that and thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
<daniroy@gmail.com> wrote in message
news:1126265381.645460.238280@g14g2000cwa.googlegroups.com...
> Dave, Max, thank you so much for your incredible explanations! Not only
> the solution is working absolutely fine but I am now also able to
> understand the logic behind it. Thanks again, Max, for the spreadsheet!
>
> Best regards
> Daniel
>