I'm using vlookup to populate a column using this formula: =IF(VLOOKUP(AA11,List!D2:D27,1,FALSE),"Pay Day","")
It works, when it sees AA11 (which is a date) matches an entry from the "List" worksheet, it populates that cell with "Pay Day".
The problem I have is when there isn't anything there it returns "#N/A". I attempted to remedy that with the IF statement-value if false "". No luck.
I have attached the file for reference.
Use the iferror function instead.
https://exceljet.net/excel-functions...error-function
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I'm doing something terribly wrong.
=IF(VLOOKUP(AA11,List!$D$2:$D$27,0),"Pay Day",IFERROR(VLOOKUP(AA11,List!$D$2:$D$27,0),""))
I'm trying to use the iferror as my "value if false" formula.
How do I use the IFERROR formula and still return my "value if true"?
As per your data, you have list.
Your pay day schedule every 14 days.
But not mention other list. What is criteria.
Secondly, for pay day : After 14 days, If it is coming "sunday" which is Holiday, then become problem. instead of that kindly set pay day on "Monday".
I have work out in your file, refer attach, Hope it is helpful for you.
Instead of vlookup use index match
In "V11"PHP Code:=IFERROR(INDEX(List!$G$2:$G$366,MATCH('2018'!$C11,List!$E$2:$E$366,0)),"")
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
To simplify I made an additional simple version.
In column A I have the date. In columns E and F I have made two lists, One for Holidays for the year and one for the Pay Days of the year. I manually populate those lists.
If a date appears in one of the two lists, I want that List header to populate in column B next to the date.
Example: Cell A2 is New Years day, 1/1/2018, so it is in the holiday list. I want Cell B2 to display "Holiday"
Example: Cell A13 , 1/12/18, is a pay day, so it is in the pay day list. I want cell B13 to display "Pay Day"
There is likely going to be an issue if I have the same date in both lists, but I'll cross that bridge (if) I get to it.
In fact, you can remove the helper column Z:AA, and using this in V11:
Also, You are having Pay Days list in sheet "List"by adding every 14 days to first Pay Day 01/01/2018. You can remove it and using this formula in V11:
Please Login or Register to view this content.
P/S: Have not noticed you update info. Will come back later.Please Login or Register to view this content.
Last edited by bebo021999; 05-11-2018 at 02:59 AM.
Quang PT
In B2:
Drag downPlease Login or Register to view this content.
That worked exactly how I was wanting. Thank you very much.
Sharing my experience, if Pay day falls into Holiday, actually Pay day will be next day.
For instant, Pay day is on 1/1/2018, it is Holiday also, so Pay day will be 1/2/2018 (next day)
Try this formula in general:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks