i have a data sheet where col c is date, col d is price the range is from
line 11 down to 300 or end of data
and a test sheet where $F$5 is a date and F8 is a price.

i am trying to write a vlookup in test sheet that will find the line in data
sheet with a date and a price that match F8&F5 in test sheet then do the
following if statement but use matching data line on data sheet instead of
line 11

=IF(C8="C",IF(AND(C8="C",B8="B"),data!H11,
data!G11),IF(AND(C8="P",B8="B"),data!P11, data!O11))

thanks

Bob

Is it something like this you´re looking for?


In test sheet in e.g. G8 this array formula

=MATCH(F5&F8,data!C11:C300&data!D11:D300,0)

Must be entered with <Shift<Ctrl<Enter also if edited later.

In e.g. H8 enter this formula:

IF(C8="C",IF(B8="B",OFFSET(data!H11,G8-1,0),
OFFSET(data!G11,G8-1,0)),IF(AND(C8="P",B8="B"),
OFFSET(data!P11,G8-1,0), OFFSET(data!O11,G8-1,0)))



--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"bob" skrev i en meddelelse
. ca...
i have a data sheet where col c is date, col d is price the range is from
line 11 down to 300 or end of data
and a test sheet where $F$5 is a date and F8 is a price.

i am trying to write a vlookup in test sheet that will find the line in
data
sheet with a date and a price that match F8&F5 in test sheet then do the
following if statement but use matching data line on data sheet instead
of
line 11

=IF(C8="C",IF(AND(C8="C",B8="B"),data!H11,
data!G11),IF(AND(C8="P",B8="B"),data!P11, data!O11))

thanks