Forum Discussion
SSBColliers
Mar 23, 2023Copper Contributor
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...
- Mar 23, 2023
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.
NikolinoDE
Mar 23, 2023Gold Contributor
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.
SSBColliers
Mar 23, 2023Copper Contributor
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.
Thanks
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.
Thanks
- NikolinoDEMar 23, 2023Gold Contributor
The 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))