Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP+IF+???
I've consulted "Help" but unfortunately it's not clear enough for amateurs.
Maybe someone can direct me to a MS webpage that covers this type of thing real well because I really struggle with these type of formulas. I don't post my question here until I've tried for some time to do it myself. Okay, here's the formula I need: =IF(C2="Yes","",(VLOOKUP(B2,NIRAV,2))) This formula is not complete for what I want and is partially incorrect. Note: "NIRAV" is a named range, which is in Col. A of another worksheet (same workbook). It is a column of numbers. I want the formula in my first worksheet to look at the number in B2 and find the corresponding number in the NIRAV range and then give me the date that is in the same row in Col. B. However, if there's no date, I want the formula to return "Not Scheduled". I can't thank you enough! Connie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP+IF+???
I am assuming the named range NIRAV covers both column A and B of the other
worksheet. =IF(C2="Yes","",IF(ISNA(VLOOKUP(B2,NIRAV,2,FALSE)) ,"Not Scheduled",VLOOKUP(B2,NIRAV,2,FALSE))) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Connie Martin" wrote: I've consulted "Help" but unfortunately it's not clear enough for amateurs. Maybe someone can direct me to a MS webpage that covers this type of thing real well because I really struggle with these type of formulas. I don't post my question here until I've tried for some time to do it myself. Okay, here's the formula I need: =IF(C2="Yes","",(VLOOKUP(B2,NIRAV,2))) This formula is not complete for what I want and is partially incorrect. Note: "NIRAV" is a named range, which is in Col. A of another worksheet (same workbook). It is a column of numbers. I want the formula in my first worksheet to look at the number in B2 and find the corresponding number in the NIRAV range and then give me the date that is in the same row in Col. B. However, if there's no date, I want the formula to return "Not Scheduled". I can't thank you enough! Connie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP+IF+???
John, this is very close to working perfectly! Just one little problem.
Because the column is formatted for a date, I get "JAN-00-00" instead of "Not Scheduled". How can we fix that? Connie "John C" wrote: I am assuming the named range NIRAV covers both column A and B of the other worksheet. =IF(C2="Yes","",IF(ISNA(VLOOKUP(B2,NIRAV,2,FALSE)) ,"Not Scheduled",VLOOKUP(B2,NIRAV,2,FALSE))) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Connie Martin" wrote: I've consulted "Help" but unfortunately it's not clear enough for amateurs. Maybe someone can direct me to a MS webpage that covers this type of thing real well because I really struggle with these type of formulas. I don't post my question here until I've tried for some time to do it myself. Okay, here's the formula I need: =IF(C2="Yes","",(VLOOKUP(B2,NIRAV,2))) This formula is not complete for what I want and is partially incorrect. Note: "NIRAV" is a named range, which is in Col. A of another worksheet (same workbook). It is a column of numbers. I want the formula in my first worksheet to look at the number in B2 and find the corresponding number in the NIRAV range and then give me the date that is in the same row in Col. B. However, if there's no date, I want the formula to return "Not Scheduled". I can't thank you enough! Connie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP+IF+???
give me the date that is in the same row in Col. B.
However, if there's no date, I want the formula to return "Not Scheduled". If there's no date is the cell empty? Possibly this: =IF(C2="Yes","",IF(VLOOKUP(B2,NIRAV,2)="","Not Scheduled",VLOOKUP(B2,NIRAV,2))) -- Biff Microsoft Excel MVP "Connie Martin" wrote in message ... I've consulted "Help" but unfortunately it's not clear enough for amateurs. Maybe someone can direct me to a MS webpage that covers this type of thing real well because I really struggle with these type of formulas. I don't post my question here until I've tried for some time to do it myself. Okay, here's the formula I need: =IF(C2="Yes","",(VLOOKUP(B2,NIRAV,2))) This formula is not complete for what I want and is partially incorrect. Note: "NIRAV" is a named range, which is in Col. A of another worksheet (same workbook). It is a column of numbers. I want the formula in my first worksheet to look at the number in B2 and find the corresponding number in the NIRAV range and then give me the date that is in the same row in Col. B. However, if there's no date, I want the formula to return "Not Scheduled". I can't thank you enough! Connie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP+IF+???
I think you need to modify John's formula to this:
=IF(C2="Yes","",IF(ISNA(VLOOKUP(B2,NIRAV, 2,FALSE)),"",IF(VLOOKUP(B2,NIRAV,2,FALSE)=0,"Not Scheduled",VLOOKUP(B2,NIRAV,2,FALSE)))) His was returning a zero for empty cells, which then gave you Jan-00-00. Hope this helps. Pete On Oct 24, 6:37*pm, Connie Martin wrote: John, this is very close to working perfectly! *Just one little problem.. * Because the column is formatted for a date, I get "JAN-00-00" instead of "Not Scheduled". *How can we fix that? *Connie "John C" wrote: I am assuming the named range NIRAV covers both column A and B of the other worksheet. =IF(C2="Yes","",IF(ISNA(VLOOKUP(B2,NIRAV,2,FALSE)) ,"Not Scheduled",VLOOKUP(B2,NIRAV,2,FALSE))) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Connie Martin" wrote: I've consulted "Help" but unfortunately it's not clear enough for amateurs. * Maybe someone can direct me to a MS webpage that covers this type of thing real well because I really struggle with these type of formulas. *I don't post my question here until I've tried for some time to do it myself. *Okay, here's the formula I need: =IF(C2="Yes","",(VLOOKUP(B2,NIRAV,2))) *This formula is not complete for what I want and is partially incorrect. *Note: "NIRAV" is a named range, which is in Col. A of another worksheet (same workbook). *It is a column of numbers. *I want the formula in my first worksheet to look at the number in B2 and find the corresponding number in the NIRAV range and then give me the date that is in the same row in Col. B. *However, if there's no date, I want the formula to return "Not Scheduled". I can't thank you enough! * Connie- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP+IF+???
Thank you, Pete. That was the missing link! That works. Connie
"Pete_UK" wrote: I think you need to modify John's formula to this: =IF(C2="Yes","",IF(ISNA(VLOOKUP(B2,NIRAV, 2,FALSE)),"",IF(VLOOKUP(B2,NIRAV,2,FALSE)=0,"Not Scheduled",VLOOKUP(B2,NIRAV,2,FALSE)))) His was returning a zero for empty cells, which then gave you Jan-00-00. Hope this helps. Pete On Oct 24, 6:37 pm, Connie Martin wrote: John, this is very close to working perfectly! Just one little problem.. Because the column is formatted for a date, I get "JAN-00-00" instead of "Not Scheduled". How can we fix that? Connie "John C" wrote: I am assuming the named range NIRAV covers both column A and B of the other worksheet. =IF(C2="Yes","",IF(ISNA(VLOOKUP(B2,NIRAV,2,FALSE)) ,"Not Scheduled",VLOOKUP(B2,NIRAV,2,FALSE))) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Connie Martin" wrote: I've consulted "Help" but unfortunately it's not clear enough for amateurs. Maybe someone can direct me to a MS webpage that covers this type of thing real well because I really struggle with these type of formulas. I don't post my question here until I've tried for some time to do it myself. Okay, here's the formula I need: =IF(C2="Yes","",(VLOOKUP(B2,NIRAV,2))) This formula is not complete for what I want and is partially incorrect. Note: "NIRAV" is a named range, which is in Col. A of another worksheet (same workbook). It is a column of numbers. I want the formula in my first worksheet to look at the number in B2 and find the corresponding number in the NIRAV range and then give me the date that is in the same row in Col. B. However, if there's no date, I want the formula to return "Not Scheduled". I can't thank you enough! Connie- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP+IF+???
You're welcome, Connie - thanks for feeding back.
Pete On Oct 24, 7:03*pm, Connie Martin wrote: Thank you, Pete. *That was the missing link! *That works. *Connie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |