how to link one columns results to other columns results depending on a condition

[juan jimenez]

Hi

What formule could link column B to the results of columns D to R, depending on the year chosen in cell B3.

Thank you very much in advance, Juan

[#2]

@juan jimenez 

Depends on which Excel you are, as variant

=XLOOKUP($B$3,$D$3:$R$3,$D$8:$R$60)

[#3]

Hi Sergei,

Thank you very much for your quick response.

I am using Excel 2016. Will xloolup work?

Another suggestion in case it doesn't?

[#4]

@juan jimenez No, XLOOKUP() is not available for 2016.

As variant, in B8 you may enter the formula

=INDEX($D$8:$R$60, 0, MATCH($B$3,$D$3:$R$3,0))

Select entire range B8:B60, in formula bar click at the end of the formula and re-enter it with Ctrl+Shift+Enter. It will be expanded as an array on entire range.

Alternatively you may use in B8

=INDEX($D8:$R8, 1, MATCH($B$3,$D$3:$R$3,0))

and drag it down till end of the range. Re-apply formatting manually or by Format Painter from other column.

[#5]

Dear Segei,

Your formula works perfectly.

Could you explain me what is the purpose of doing "elect entire range B8:B60, in formula bar click at the end of the formula and re-enter it with Ctrl+Shift+Enter. It will be expanded as an array on entire range."?

And what would be the difference with the second option?

thank you, Juan.

[#6]

@juan jimenez 

Yes, with first option you expand an array on entire range.

With second option you copy the cell by this or that down, not only the formula but formatting as well.

However, above are some variants, each option you may perform by several ways. For example, for second option copy cell B8, select B8:B60, Paste->Paste special, select Formula, ok