Unable to use index and match function to fetch values

[vivektripathy20]

I want to fetch total revenue based on ticker symbol and year
I am using the forumla =INDEX(Total_Revenue,MATCH(1,($C$2=Tickersymbol)*(F6=Years),0),1)
but i am getting error a value is not available in profit and loss statement
Please check 4th tab profit and loss statement

[XLent]

couple of things

- your cell reference to "year" is creating a circular reference as referring to incorrect row
- the use of the MATCH, in your original, requires Array entry (Ctrl + Shift + Enter)

however, given your version of XL, and the nature of the calculation, you should use SUMIFS

Please Login or Register  to view this content.

this will be a cleaner, more efficient, approach