Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with if statement together
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with if statement together
Try like this in d1 copied down
=LOOKUP(2,1/((A1=Sheet1!A$1:A$100)*(B1=Sheet1!B$1:B$100)),Shee t1!C$1:C $100) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with if statement together
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) -- Biff Microsoft Excel MVP "fruitchunk" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with if statement together
Thanks to all of you, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with if statement together
You're welcome!
-- Biff Microsoft Excel MVP "fruitchunk" wrote in message ... Thanks to all of you, |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with if statement together
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with if statement together
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. -- Biff Microsoft Excel MVP "fruitchunk" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using vlookup within an if statement | Excel Worksheet Functions | |||
Vlookup with if statement | Excel Discussion (Misc queries) | |||
If Statement / VLookup | Excel Worksheet Functions | |||
Vlookup with If Statement | Excel Worksheet Functions | |||
If Statement with VLookup | Excel Worksheet Functions |