Lookup 2 values to return 1

[Sarah]

Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to
lookup 2 different dates in a table and if both of those dates appear on one
sheet, I need the associated value to be returned. If this is possible, how
would I do this?

 

[Eduardo]

Hi,
An example will help but assuming that you want to extract a value from
column C if the dates in column D and E match your dates entered in cells A1
and B1 and put the value in A2 use in A2

=sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000)

 

[Sarah]

Hi,

What I am looking at cell A1 and B1 from Sheet1 and looking up those two
dates in a table array on Sheet2. If both of those dates are found in lets
say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to
be returned on Sheet1. Does this help at all?

 

[Eduardo]

Hi Sarah,
try


=sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000)

formula will look what you have in cell a1 and b1 through columns B and C in
sheet2 and when match is found will get total from column D

Hope this helps

 

[Glenn]

Yes. Modify Eduardo's formula to fit your ranges and layout. Something like this:

=SUMPRODUCT(--(A1=Sheet2!$B$1:$B$1000),--(B1=Sheet2!$C$1:$C$1000),Sheet2!$D$1:$D$1000)

 

[Sarah]

That didnt work. The return value is actually a text so a sum wouldnt work.
Thanks though!

 

[Glenn]

The more information you provide up front, the easier it is to provide a working
solution.

Try this array formula (commit with CTRL+SHIFT+ENTER):

=INDEX(Sheet2!$B$2:$D$1000,MATCH(A1&B1,
Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0),3)

 

[Glenn]

Actually, the column selection in the INDEX() is not necessary if we define the
array as just column D:

=INDEX(Sheet2!$D$2:$D$1000,MATCH(A1&B1,
Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0))

 

[Eduardo]

Hi,
try

=if(sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000))>0,sheet2!$D$1:$D$1000)

 

[Sarah]

i think that worked. thanks!

The more information you provide up front, the easier it is to provide a working
solution.

Try this array formula (commit with CTRL+SHIFT+ENTER):

=INDEX(Sheet2!$B$2:$D$1000,MATCH(A1&B1,
Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0),3)


.