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
--