Forum Discussion
How to make index function only pull figures if cell adjacent hits required criteria
My suggested solution would be, you can use the INDEX and MATCH functions with multiple criteria to look up values based on the year.
You can use this generic formula syntax:
=INDEX(return_range,MATCH(1,(criteria1=range1)(criteria2=range2)…),0))
In your case, you can use something like this:
=INDEX(A1:A100,MATCH(1,(B1:B100=year),0))
Where year is the cell that contains the year you want to check.
This is an array formula and must be entered with Control + Shift + Enter in Legacy Excel or just Enter in Excel 365.
My suggested solution would be, you can use the INDEX and MATCH functions with multiple criteria to look up values based on the year.
You can use this generic formula syntax:
=INDEX(return_range,MATCH(1,(criteria1=range1)(criteria2=range2)…),0))
In your case, you can use something like this:
=INDEX(A1:A100,MATCH(1,(B1:B100=year),0))
Where year is the cell that contains the year you want to check.
This is an array formula and must be entered with Control + Shift + Enter in Legacy Excel or just Enter in Excel 365.
Unfortunately, this didn't seem to work. I forgot to disclose that the table with the raw data is on a separate sheet. Does this have an effect on the formula? I get an "#N/A" error.
Perhaps I've entered the ranges wrong.
The range for the name is:
P3:P310
The range for the year is:
U3:U310
The table with the raw data is formatted as a table. Does this also have an effect?
