Nested IF, OR, and VLOOKUP formula is not distinguishing text.

[JEH105]

I built a PTO calendar dashboard where it automatically updates the employees availability for the day based on the calendar's data. The options vary from U (unscheduled), holiday, jury duty, covering, or a value over 1 is added. This is what it looks like:

=IF(OR(VLOOKUP(N28,table info,6,0)="U",
VLOOKUP(N28,table info,6,0)="Holiday",
VLOOKUP(N28,table info,6,0)="Jury Duty",
VLOOKUP(N28,table info,6,0)="Covering",
VLOOKUP(N28,table info,6,0)>=1),"Time Off","Available")

*N28 is the the cell where the current date is located.

The issue is, that it doesn't distinguish the text. There is another text included in the data "Coverage N/A" and it counts it, it should be ignores as it is not included in the formula (or so I think). I tested and added random text "blah" and it would also mark the employee off for that day. It just doesn't actually distinguish the text, it counts any type of data on the cells.

Does anybody know what I'm doing wrong and what I can do to fix it? I truly appreciate any help!

Sincerely, an amateur. ? *sigh*

 

[Radoslaw Poprawski]

minisheet please

 

[Michael M]

Make sure N28 and "Table info" are in the same format if they are dates
AND make sure the VLOOKUP is looking in the correct column...otherwise you will need to supply a sample of data as requested by @Radoslaw Poprawski

 

[JEH105]

minisheet please

Thank you for the tip! I am unable to post a minisheet, because I'm using my work laptop (a lot of copyright limitations). However, I'll make a dummy version on my personal laptop and post it here tonight.

 

[JEH105]

Make sure N28 and "Table info" are in the same format if they are dates
AND make sure the VLOOKUP is looking in the correct column...otherwise you will need to supply a sample of data as requested by @Radoslaw Poprawski

I double checked to make sure and yes, both the format from the table and N28 are the same and it's pulling from the correct column. ? I'll have to make a replica of the workbook to post on here for assistance. Thank you for your help!

 

[JEH105]

I built a PTO calendar dashboard where it automatically updates the employees availability for the day based on the calendar's data. The options vary from U (unscheduled), holiday, jury duty, covering, or a value over 1 is added. This is what it looks like:

=IF(OR(VLOOKUP(N28,table info,6,0)="U",
VLOOKUP(N28,table info,6,0)="Holiday",
VLOOKUP(N28,table info,6,0)="Jury Duty",
VLOOKUP(N28,table info,6,0)="Covering",
VLOOKUP(N28,table info,6,0)>=1),"Time Off","Available")

*N28 is the the cell where the current date is located.

The issue is, that it doesn't distinguish the text. There is another text included in the data "Coverage N/A" and it counts it, it should be ignores as it is not included in the formula (or so I think). I tested and added random text "blah" and it would also mark the employee off for that day. It just doesn't actually distinguish the text, it counts any type of data on the cells.

Does anybody know what I'm doing wrong and what I can do to fix it? I truly appreciate any help!

Sincerely, an amateur. ? *

PS: It looks like when I remove the ">=1" criteria from the formula, it works. However, I need that criteria along with the text ones "holiday", "covering", etc.

Will someone please help me find a workaround or if this is not the issue, correct me? Thank you for your time!

 

[Alex Blakenburg]

Without data we are only guessing and can only give partial advice which may have pitfalls.
As it stands the formulas does not seem to be employee specific (no employee ID referenced) and anything that is not referenced "defaults to available".

The equation ="a">=1 returns TRUE so anything that is text will make the greater >=1 TRUE in your vlookup version.

WIthout knowing more about the data, replacing the final vlookup that uses the ">=1" with the below should work.
(The COUNTIFS, SUMIFS etc formulas tend to handle values that look like numbers as numbers)

=COUNTIFS(Table1[@[Table_Col1]],$N26,Table1[@[Table_Col6]],">=1")>=1

You can dispense with the final >=1 that is outside the brackets of the COUNTIFS if you want to since any number that is not zero "0" will be evaluated as TRUE.
Personally I find including the ">=" version clearer.

 

[Michael M]

AND, Is the last VLOOKUP looking at Exactly >= 1 ??

VLOOKUP(N28,[I]table info,[/I]6,0)>=1),"Time Off","Available")

Is N28 a text value of >=1 or is it an actual number?
Is the number in the cell accurate......eg, what if the actual number in the cell is 0.9, but appears as 1.......this would then give an incorrect response to the formula.
BUT, as @AlexBlakenburg has pointed out...without useful data we are all only guessing !!