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

JEH105

New Member
Joined
Oct 11, 2019
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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*
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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)
Excel Formula:
=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.
 
Upvote 0
AND, Is the last VLOOKUP looking at Exactly >= 1 ??
Excel Formula:
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 !!
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top