Two Reference Cells in VLOOKUP, Probably easy but ....

[Guest]

Hi,

I want to make a formula probably using Vlookup, but not necessarily, that
uses two reference cells, i.e a date and a line number, looks at another
table matches the correct values to the required reference cells and returns
some other required data.



Line Date Required Field
A 01/06/2006 1
B 01/06/2006 2
C 01/06/2006 3
A 02/06/2006 4
B 02/06/2006 5
C 02/06/2006 6


i.e I want the required field to be returned when I stipulate that I am
looking for Line "B" and Date"2/6/06" and returns the number "5"

This is probably very easy but I'm afraid I just don’t know how to do it.

Many Thanks,
P

 

[Ardus Petus]

=SUMPRODUCT((A1:A10="A")*(B1:B10=--"02/06/06");C1:C10)

HTH

 

[Guest]

Hi Ross,

try =sumproduct(--($A2$2:$A$100="B")*($B2$2:$B$100=date)*($C$2:$C$100))


HTH
regards from Brazil
Marcelo

"Ross" escreveu:

 

[Guest]

Hi Ross,

try =sumproduct(--($A2$2:$A$100="B")*($B2$2:$B$100=date)*($C$2:$C$100))


HTH
regards from Brazil
Marcelo


"Ross" escreveu: