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?

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

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!