Find value in row and retrieve adjacent cell

[DigDoug]

Hi Everyone,

I'm having a problem that has been plaguing me for some time.

I have a spreadsheet of Years, with the cost associated with each year for each particular entry (row). Each row has a different set of years in each column, but I am looking to return the amount for a specified year in just one column at the end.

Before I confuse you more, maybe the attached spreadsheet will help explain.

Lookup in Array.xlsx

I have manually populated Column L to illustrate what I am trying to do. Does anyone happen to know a formula that can do this?

Happy to answer any questions if I can help clarify.

Thank you.

[hoyasaxa215]

Column L in the file you posted is blank. Did you manually populate Column K?

[hoyasaxa215]

Also, does the data need to stay organized as you have it? Or can it be rearranged? It'd be much easier if the data could be organized into two columns.

[Zodeeak]

=SUMIF(Table2[[#This Row],[Year 1]],K$1,Table2[[#This Row],[Amount]])+SUMIF(Table2[[#This Row],[Year 2]],K$1,Table2[[#This Row],[Amount2]])+SUMIF(Table2[[#This Row],[Year 3]],K$1,Table2[[#This Row],[Amount3]])+SUMIF(Table2[[#This Row],[Year 4]],K$1,Table2[[#This Row],[Amount4]])+SUMIF(Table2[[#This Row],[Year 5]],K$1,Table2[[#This Row],[Amount5]])

This is the formula I used and it can be copied across multiple columns to account for more years to come.

Hope that helps.

-Z

[DigDoug]

Column L in the file you posted is blank. Did you manually populate Column K?

Yes, Sorry about that. I mentioned the column in an older version of the sheet I was using.

Nice to see a fellow georgetown alum!

[DigDoug]

=SUMIF(Table2[[#This Row],[Year 1]],K$1,Table2[[#This Row],[Amount]])+SUMIF(Table2[[#This Row],[Year 2]],K$1,Table2[[#This Row],[Amount2]])+SUMIF(Table2[[#This Row],[Year 3]],K$1,Table2[[#This Row],[Amount3]])+SUMIF(Table2[[#This Row],[Year 4]],K$1,Table2[[#This Row],[Amount4]])+SUMIF(Table2[[#This Row],[Year 5]],K$1,Table2[[#This Row],[Amount5]])

This is the formula I used and it can be copied across multiple columns to account for more years to come.

Hope that helps.

-Z

Thanks, this worked and was something I was thinking about as well. I was just hoping that there would be a "prettier" solution code-wise.

[Zodeeak]

There will likely always be a more elegant way to do things when I post something. I just use what little I know and works for me.

-Z