Vlookup based on cell content

[AngleseyExcel]

=VLOOKUP(AA10,'Week 52'!$F$8:$F$105,1,0)

i'm using the above to check that selection on sheet 'Week 52'. but i want it to be dependent on whichever week number is in a certain cell. not fixed on one single sheet.

Can anyone give me any pointers on how this would be done ?

 

[AngleseyExcel]

https://1drv.ms/x/s!AnoWKc02O52ckxG0Uv7G1RQ8mrgw?e=2kkNGY

This is a link to the a test file that i'm using.

You'll find 2 worksheets in the file. In the checker worksheet, you'll find a box that will show if those duties are covered on those days in whichever sheet is selected in the dropdown. what i'm looking at getting to work is getting the green and red boxes in the checker sheet to changes based on what the indirect finds.

TIA for any and all help.

 

[jasonb75]

As far as I can see, INDIRECT is not the problem there are no matching records in the lookup column for the criteria you are searching for.

The suggested formulas will all work when there is matching data.

If you think that there is a result that should be found then please tell us which cell in the 'Checker' sheet should show a result and where you think that the result should be found in the 'Week 52' sheet.

 

[etaf]

can you provide details , what cell is the lookup using indirect IN ?

 

[AngleseyExcel]

what i'm trying to get it to do is, if it checks the sheet(Week 52) for whatever is in the cell to the left of the red/green cell in 'Checker' to return either a 1 if found or 0 if not found. I can then use conditional formatting to colour them green or red respectively. But all this is slightly over my head, and it's why i'm sounding a bit confused.

 

[jasonb75]

whatever is in the cell to the left of the red/green cell in 'Checker' to return either a 1 if found or 0 if not found.

In that case you would be better of with countif than vlookup (as mentioned in post 3)

=COUNTIFS(INDIRECT("'Week "&A1&"'!$F$8:$F$105")

Vlookup will return the original text, or an error if nothing is found, not 1 or 0 as required. If you have based your conditional formatting on values of 1 and 0 then it will fail because of the formula results not meeting the criteria.

 

[etaf]

as mentioned by jasonb75, use a countif() with the indirect

 

[AngleseyExcel]

Thank yo all for your replies. The countif worked perfectly.