Help with VLOOKUP

I am trying to pull data from one sheet into a summary sheet. Basically I am looking to pull if a given site met a target on a given day -- an admin is entering in the number of employees reporting for work on a day for each of 12 sites into a Smartsheet (called KU MR Attendance), and I need to see if they are meeting the target for the day.

I am using the following formula:

=VLOOKUP(Helper3,{KU MR Attendance Range 6},1)

where: Helper3 is the site concatenated with the day (ie. Campbellsville|10/11/21); {KU MR Attendance Range 6} is the range from the KU MR Attendance sheet that has a similar Helper column and the target met status.

The problem as shown above is that the top 5 are returning #NO MATC ... even though they are in the source sheet, the Lexington line is showing correct, the next two are inaccurate, and the final four are correct. Please note, the current VLOOKUP formula is pulling column 1 simply to troubleshoot ... once it starts working, I will be changing this to column 2.

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

All comments

Parker Oxford

I know in excel, using VLOOKUP can be finicky in what is looking for, so for situations very similar to this one, I use INDEX and MATCH together to get the desired find.

Paul Newcome goes over this very well in his response HERE

James Keuning

I don't think this will fix it, but can you change the reference to your Helper field to Helper@row?

I agree with the recommendation to use INDEX and MATCH, but we should be able to get the VLOOKUP to work.

mpfarrer

James, I changed the Helper@row and it did not fix it.

Parker, James,

Here is my first attempt at using Index and Match ...

=INDEX({KU MR Attendance Range 17}, MATCH(Helper@row, {KU MR Attendance Range 17}), 1)

KU MR Attendance Range 17 encompasses Helper and Target Met from the source sheet.

Results are ...

Screen Shot 2021-11-02 at 3.20.20 PM.png

Genevieve P.

Hi @mpfarrer

Try adding a 0 to the end of your MATCH function, to let the formula know that your list is not sorted in a descending manner but is mixed:

=INDEX({KU MR Attendance Range 17}, MATCH(Helper@row, {KU MR Attendance Range 17}, 0), 1)

If this doesn't work, try using a COUNTIF to see if the two values are matching across your sheets:

=COUNTIF({KU MR Attendance Range 17}, Helper@row)

If this is returning 0, then there's something different about the values in your two sheets and we'll have to look into this further.

Cheers,

Genevieve

mpfarrer

Genevieve,

So the good news is that Countif returned 1 for each. The bad news ... this is the result of adding a 0 ...

Screen Shot 2021-11-03 at 3.49.48 PM.png

Mike

Genevieve P.

Hi @mpfarrer

My apologies, in reviewing your original post I'm not sure I understand what you're looking to do.

I see that you have the same range listed here:

=INDEX({KU MR Attendance Range 17}, MATCH(Helper@row, {KU MR Attendance Range 17}, 0), 1)

The intent of this formula is to pull a different column's data based on a match across sheets in a matching column. So in your case, you could pull back the "Target" column based on your current column.

If instead you're just looking to see if there is a match, the COUNTIF would actually be perfect! You could say IF there's a count of 1, return the same cell. If not, return blank, like so:

=IF(COUNTIF({KU MR Attendance Range 17}, Helper@row) > 0, Helper@row, "")

Is this what you're looking to do? Return the exact same value?

Cheers,

Genevieve

mpfarrer

Genevieve,

I am trying to pull the former not the later ...

"... to pull a different column's data based on a match across sheets in a matching column. So in your case, you could pull back the "Target" column based on your current column."

I am looking to pull if a given site met a target on a given day -- an admin is entering in the number of employees reporting for work on a day for each of 12 sites into a Smartsheet (called KU MR Attendance), and I need to see if they are meeting the target for the day.

Mike

Genevieve P.

HI @mpfarrer

Ok, so what column contains the information about if the target is met or not? That's the column you want listed as the first range:

=INDEX({Column to Return}...

Then you use the MATCH function to find the row that matches between the two sheets:

MATCH((Helper@row, {KU MR Attendance Range 17}, 0)

What you're missing is the Column to Return - it can't be the same as the one you're matching.

=INDEX({Column to Return}, MATCH(Helper@row, {KU MR Attendance Range 17}, 0), 1)

Does that help clarify?

Cheers,

Genevieve

mpfarrer

Genevieve,

YOU ARE AWESOME that fixed it. Thank you for everyone's help. Apparently in my quest to troubleshoot, it confused the system (using the same column to match as the one to return).

Thank you Genevieve, James and Parker,

Mike

Genevieve P.

No problem at all! I'm glad I was able to help 🙂

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.