LOOKUP+IF+???

[Connie Martin]

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

 

[John C]

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)))

 

[Connie Martin]

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

 

[T. Valko]

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)))

 

[Pete_UK]

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

 

[Connie Martin]

Thank you, Pete. That was the missing link! That works. Connie

 

[Pete_UK]

You're welcome, Connie - thanks for feeding back.

Pete