vlookup on double criteria

  • Thread starter Thread starter daniroy
  • Start date Start date
D

daniroy

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))
 
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>
 
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!
 
It's a pleasure to be able to help out a little, one who has helped
countless others many times over, including this writer !
 
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.
 
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
 
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
 
Back
Top