Additional check for current year

[Tej92]

Hello all, how can i adjust this formula to also look for the year value in the column A of the same sheet and need to match it to the current year.
it needs to confirm both cases.
=IFERROR(IF(ISNA(VLOOKUP(I$2,INDIRECT("'"&TEXTBEFORE($A3," ",2)&"_data'!D:D"),1,FALSE)),AV3,"Yes"),AV3)

 

[awoohaw]

add this to your formula: IF(Year(A3) = Year(Today()) ..... insert your formula... , "")

=if(Year(A3)=Year(Today()),IFERROR(IF(ISNA(VLOOKUP(I$2,INDIRECT("'"&TEXTBEFORE($A3," ",2)&"_data'!D:D"),1,FALSE)),AV3,"Yes"),AV3),"")

Note: Use of many volatile functions will slow performance of your workbook considerably (INDIRECT and TODAY) the larger and larger the workbook gets.

 

[Tej92]

Hi, this formula needs to perform a multiple vlookup, in sheet where the column D is being checked in.
in a3 is the name of the sheet.
in that sheet's column D is the week numbers
column A has dates.
i need the formula to check for the week number and year and if found for the current year to return yes.

@awoohaw thank you for the formula but this doesn't work, i definitely didn't explain properly, apolofies.

 

[awoohaw]

You did not mention week number in your OP. How about posting a mini worksheet using hte xl2bb addi in (link below in signature) for each of your worksheets? Just a portion of the data. if you can't use xl2bb then copy and paste selected cells from your workbooks sheets. Don't post an image as the forum must completely recreate your scenario.

Do you have any kind of working formula that uses the week number?

 

[Tej92]

This is sheet 1

Week	2	4	6	8	10	12	14	16
Area 1	Jo Smith	Yes	Jo Smith	Yes	Yes	Yes	Yes	Yes
Area 2	No Smith	Yes	No Smith	Yes	Yes	Yes	Yes	Yes
Area 3	Go Smith	Yes	Jo Smith	Yes	Yes	Yes	Yes	Yes

this for example is Area 1_data

Date	Auditor	Score	Week
20/04/2023​	Jo Smith	83.33%​	16​
10/05/2023​	Go Smith	94.44%​	18​
11/05/2023​	No Smith	100.00%​	18​

this is the current formula that works and gives me the name of the person from another table, if Area 1_data does not have the audit done it will leave the name in place otherwise write Yes
=IFERROR(IF(ISNA(VLOOKUP(I$2,INDIRECT("'"&TEXTBEFORE($A3," ",2)&"_data'!D:D"),1,FALSE)),AV3,"Yes"),AV3)

thank you for looking into it

 

[Tej92]

any help would be greatly appreciated

 

[Tej92]

Any guidance would be appreciated!
thanks in advance