Vlookup function

Jessica Howell

Hello all,

I am looking for a formula that see's the week number, and therefore populates the period in the box above it

Everything is listed on this sheet below, each week has a corresponding period, all the way to the end of 2022

I tried using,

=VLOOKUP($[2]$3, {IN. Data Control (Dates) 2019-22 Range 2}, 4, false)

But it comes up no match

Suggestions?

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Jessica Howell

I would use an INDEX(COLLECT formula instead!

First you list the column with the information you want to bring back, then you list each column and criteria after it as a sort of filter.

Try something like this:

=INDEX(COLLECT({Period Column}, {Week Column}, $[2]$3, {Year Column}, $[2]$1), 1)

Cheers,

Genevieve

All comments

Paul Newcome

Exactly which column are you matching on and which column are you pulling from?

Jessica Howell

Hi @Paul Newcome

Based on the Week and year In "Four week running" I need it to fill the period field, based on the matching data in "IN. Data control"

So, currently there's year 2021, and week 33, I need it to match with criteria in the control data sheet to fill the period field

Genevieve P.

Hi @Jessica Howell

I would use an INDEX(COLLECT formula instead!

First you list the column with the information you want to bring back, then you list each column and criteria after it as a sort of filter.

Try something like this:

=INDEX(COLLECT({Period Column}, {Week Column}, $[2]$3, {Year Column}, $[2]$1), 1)

Cheers,

Genevieve

Paul Newcome

@Jessica Howell My apologies. I missed the notification that you had responded. Does Genevieve's solution work for you?

@Genevieve P. Thanks again.

Jessica Howell

@Genevieve P.

It worked, thank you!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.