VLOOKUP for numerical values
Hi All,
I am using VLOOKUP to retrieve numerical values from another sheet, the formula works, however when I have '0' in a cell, it pulls '1' also, which is the true value of the VLOOKUP. When I use the same logic with excel it works w/o any issues & brings the '0' value.
What should I fix to get the same results?
Thanks - Viktor
Answers
-
Are you able to provide some screenshots that show what the issue is?
-
Hi Paul,
sure - so below is the result of the formula: =VLOOKUP(Alias@row, {Attendance_Raw_Data_Q1. Range 1}, 11, false) - 'Alias@row' is a column with logins - 'Attendance_Raw_Data_Q1' is the reference sheet, where working days/bank holidays/weekends etc. are tracked - the 6th of January was in this particular country a bank holiday, however VLOOKUP says that day worked is '1' = 100%, which is not true, since reference sheet stats that this is '0' = 0%.
Below you can see the record on the reference sheet for the same login.
When I did the same action on excel, the VLOOKUP showed me '0', what's the correct value.
I also tried the above formula, with other columns, before the 11th column & worked well, everytime it retrieved the correct data.
I assume the problem here is that usually '0' means false & '1' true, so VLOOKUP says that the value has been found = '1' also when in the cell is actually '0', so basically '0' will be skipped or with other words marked as found = '1'.
But maybe I am wrong.
Thanks - Viktor
-
How are the dates entered on each of the sheets? Also double check that the date is not listed elsewhere in that column on the source sheet.
-
The date formatting is the same on both sheets & I have one date more times on the sheet, since i have 8000+ rows, but every single row contains also the login.
That's the structure of both files - 1 alias in 30/31 rows & than the next alias with the same dates - the VLOOKUP runs based on alias an not on date.
-
My apologies. I was looking more at the screenshots and didn't see an Alias column. The VLOOKUP is going to pull based on the topmost occurrence of the Alias. Even if the Alias is listed multiple times, it is always going to pull the same result.
After re-reading everything, your formula isn't looking at dates at all, so there is no reason for it to compare them.
-
No worries at all. How could I manage it than with SUMIF?
Simultaneously I would like to get also the dates & week numbers to the other sheet, but I am really struggling with it.
-
I'm not sure I understand exactly what you are trying to accomplish. Are you able to provide screenshots with all pertinent columns visible including the alias column as well as a detailed description of what exactly should be happening?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!