Matching two variables in the same column for a lookup

[AllMB]

This is my first post so please work with me a bit.


I am trying to use two variables in the same column (different rows, one is a header and one is a list item) to return a number in the corresponding row of the second variable (list item) in a spreadsheet. This data is also in a pivot table that is made the same way each time the spreadsheet is made.

Below is a smaller version what I am working on. Had to remove some titles and replace for privacy purposes.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Capital Projects[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Capital​

[/TD]
[TD]1427[/TD]
[TD]1586[/TD]
[TD]8623[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Cntrct_SVC​

[/TD]
[TD]1548[/TD]
[TD]2569[/TD]
[TD]2134[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Consum​

[/TD]
[TD]800[/TD]
[TD]757[/TD]
[TD]1557[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Fees&Charges​

[/TD]
[TD]215[/TD]
[TD]0[/TD]
[TD]215[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Oth_Exp​

[/TD]
[TD]325[/TD]
[TD]561[/TD]
[TD]956[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Services​

[/TD]
[TD]412[/TD]
[TD]564[/TD]
[TD]951[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Utilities​

[/TD]
[TD]357[/TD]
[TD]654[/TD]
[TD]753[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Cntrct_SVC​

[/TD]
[TD]158[/TD]
[TD]956[/TD]
[TD]231[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Consum​

[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Fees&Charges​

[/TD]
[TD]112[/TD]
[TD]962[/TD]
[TD]250[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to write a formula that will find "Capital Projects" and then find any of the list items below it and the match it to the first, second, or third column. This data is in a pivot table that is 400 rows long with 15 different headings and a group of lists under each heading. I am trying to make a spreadsheet that is almost idiot proof for those I work with and also that is standardized for each department. I

I have tried nesting match inside of an index inside of an if statement and I just cannot seem to make it work. Any help you can give would be appreciated.

Also tried to put in a screenshot and could get it to work.

 

[edmitchell]

Hi

so for the example data you have shown what do you want the formula to return?

 

[AllMB]

I want the formula to return any of the numbers based on a relative reference. The titles of the columns are "Budget, Expenditures, Encumbrance". I will need the formula to return any of the cells that are numbers.