Matching Values

[Manesh]

Dear Chandoo,

I am trying to match a Year from a date to a corresponding Year in a data array and then return the corresponding average value.

Hence in Cell D2, i should obtain the value of 77 from Column B because the date in C2 falls within the year in A7.

Please see attached data sheet.

Thank you.

Best regards,

Manesh

 

[Somendra Misra]

Hi Manesh,

Try below array formula in D2: Confirm with Ctrl+Shift+Enter.

=INDEX($B$2:$B$11,MATCH(YEAR(C2),YEAR($A$2:$A$11),0))

Regards,

 

[Manesh]

Dear Somendra,

Please look at the attached spreadsheet - Book 1. I have applied your logic with some modification in sheet 2 but I am experiencing an error message. Can you please assist me with this.

Thank you.

Best regards,
Manesh

 

[Somendra Misra]

@Manesh

I don't see any error in your file.

Regards,

 

[Manesh]

Hi Somendra,

Please look at Sheet 2 of the new file that I uploaded. The file name is Book 1.

The following cells show an error on Sheet 2:

• E4
• E7
• E11
• E14

I have reattached the file to this message as well.

Thank you.

Best regards,
Manesh

 

[Somendra Misra]

The formula must be entered as an array formula, so after typing the formula you must press Ctrl+Shift+Enter and not just enter. If your formula is entered correctly , you can See { .. } around the formula in formula bar.

Regards,

 

[Manesh]

Hi Somendra,

Thank you for your ongoing assistance. I have a final request for the matching. I have uploaded a new file titled Book 2

I am trying to achieve the following:

• Match the Year for the date in Column A of Sheet 2 to the Year in Column A of Sheet 1
• The return the Average Yield from Column C in Sheet 1 for that year to Sheet 2 in Column G

Thus in Sheet 2 - Cell G18, we must get the value of 13.040% from Sheet 1 - Cell C263.

This is because the Year in A18 is 1982 and the Average Yield for 1982 from Sheet 1 was 13.040% in Cell C263.

Your assistance with is much appreciated as I have tried to transpose your earlier logic but I am getting blank cells.

Thank you for your patience.

Best regards,

Manesh

 

[Somendra Misra]

@Manesh

Just one quick question, on Sheet2 you have shown dates in column A in intermittent cells and in between you have shown blanks cells, in your original file will there be blank cells or some data, if data than what?

Regards,

 

[Manesh]

Hi Somendra

At the moment the cells are blank. However, it is possible that as new data becomes available that these cells can be filled. If they are filled, it will be the same type of data that is currently in the column.

Hence in this case it would be dates.

Thank you.

Manesh

 

[Manesh]

Hi Somendra,

Do you believe a solution to my request above is possible? I am unable to solve for it.

Thank you.
Manesh

 

[Somendra Misra]

@Manesh Hi,

Give me some time till eve today to work on it. Right now busy with personal official work

Regards,

 

[Manesh]

Thank you Somendra,

I look forward to your solution.

Best,
Manesh