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

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots that show what the issue is?

Viktor Palles

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

Paul Newcome

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.

Viktor Palles

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.

Paul Newcome

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.

Viktor Palles

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.

Paul Newcome

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.