vlookup with if statement together

F

fruitchunk

I need help to do a vlookup with if statement together
Sheet 1 - Column A: Dates, Column B: Names:, Column C: Control Numbers,
Sheet 2 - Column A: Dates, Column B: Names, Column C: Code Numbers,
In Column D on sheet 2, I want to see the Control Number for the specific
Name and specific Date.

This is what I have in D1 =IF(A1=Sheet1!A:A,VLOOKUP(B1,Sheet1!$A$1:$C$100,3))
Instead of getting the Control Number I get FALSE
What am I doing wrong? Can you please help?
 
B

barry houdini

Try like this in d1 copied down

=LOOKUP(2,1/((A1=Sheet1!A$1:A$100)*(B1=Sheet1!B$1:B$100)),Sheet1!C$1:C
$100)
 
T

T. Valko

Try this array formula** :

=INDEX(Sheet1!C$1:C$100,MATCH(1,(Sheet1!A$1:A$100=A1)*(Sheet1!B$1:B$100=B1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
F

fruitchunk

Now it gets a bit more complicated. I have to use Start Date and End Date on
sheet1

Sheet 1 - Column A: Start Date, Column B: End Date, Coulmn C:Names:, Column
D: Control Numbers,
Sheet 2 - Column A: Date, Column B: Names, Column C: Code Numbers,
In Column D on sheet 2, I want to see the Control Number for the specific
Name and specific Date if the date on sheet2 either matches the start or
end date or is between the dates.

Thanks
 
T

T. Valko

Now it gets a bit more complicated.

Oh yeah, it always gets more and more complicated!!!

Try this array formula** :

=INDEX(Sheet1!D2:D10,MATCH(1,(A2>=Sheet1!A2:A10)*(A2<=Sheet1!B2:B10)*(B2=Sheet1!C2:C10),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top