Forum Discussion
Solved
How to make index function only pull figures if cell adjacent hits required criteria
I'm currently using this formula: =INDEX(A1:A100,MODE(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0)))) From this website: "https://www.extendoffice.com/documents/excel/3397-excel-find-mode-for-text.html...
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.
- Hi Nikolino. This worked.
I have another question. Very similar. Would you suggest i post it here? Or should i create a new discussion. I think you'll be able to answer it very easily as it is basically the same q.
ThanksThe formula you entered is not working because you need to specify the sheet name where the raw data is located. For example, if the sheet name is “Data”, you can use this formula:
=INDEX(Data!P3:P310,MATCH(1,(Data!U3:U310=year),0))
Also, make sure that the year value is a number and not text.
You can use the VALUE function to convert text to number if needed.
The table format does not affect the formula, but you can use structured references instead of cell ranges if you want. For example, if the table name is “Table1”, you can use this formula:
=INDEX(Table1[Name],MATCH(1,(Table1[Year]=year),0))
- Thank you for your response.
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?
