Forum Discussion

juan jimenez's avatar
juan jimenez
Iron Contributor
May 10, 2021

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

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

excel
Formulas and Functions

5 Replies

    • juan jimenez's avatar
      juan jimenez
      Iron Contributor
      May 10, 2021
      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?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 10, 2021

        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.

Resources