How to make index function only pull figures if cell adjacent hits required criteria

[SSBColliers]

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" 

This worked well for an overall modal value. But my figures (range A1:A100) have an adjacent cell with the years next to it. I want to create a table to provide an automated modal value for each year. 

Eg:

Value	Year
Y	2020
Y	2020
X	2019
X	2019
X	2019
X	2018

In this example, the formula would return a mode of X, however, this X is only the mode for the entire table, and for years 2018 and 2019. In 2020 there were no cases of X, only Y. 

I want the formula to be able to check the year in the adjacent cell from the entire table, and only output the modal value for that specific year in this approximate format:

Year	2018	2019	2020	Total
Mode	X	X	Y	X

Is there any way to incorporate this into the formula or will i need to manually create new tables per year? VLOOKUP perhaps?

[#2 · Accepted solution]

@SSBColliers 

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.

[#3]

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?

[#4]

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

[#5]

@SSBColliers 

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))