Lookup 2 values to return 1

  • Thread starter Thread starter Sarah
  • Start date Start date
S

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?
 
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)
 
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?
 
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
 
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)
 
That didnt work. The return value is actually a text so a sum wouldnt work.
Thanks though!
 
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)
 
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))
 
Hi,
try

=if(sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000))>0,sheet2!$D$1:$D$1000)
 
i think that worked. thanks!

Glenn said:
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)


.
Click to expand...
 
Back
Top