vlookup

[bob]

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

 

[Leo Heuser]

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!D11300,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.