CODEWITHSUNDEEP
excelindexingmatch
tnpir4002
tnpir4002

Reputation: 83

How to do lookups in Excel with multiple criteria

I have a question. I'm collecting social media statistics (likes, shares, comments, etc.), and one thing that I'm trying to do is get Excel to tell me the date that the largest number of hits occurred, what post it was on, and what type it was.

I'm part of the way there--I can get Excel to tell me what the highest number is (MAX, easy!), but I can't get it to lock in the date properly.

The way I've got it set up is: there's a single row for each day of the month, with the dates in column A, and additional columns for the types of engagements I'm tracking. At the very bottom of the table is a row with all the MAX values from each column (so in other words, column B will have Likes, column C is shares, and so on, so I can enter a new value for each analytic every day.

Essentially, what I'm trying to do is get Excel to do this, in order:

  1. Look along the row with all the MAX values to find the highest value;
  2. Look up above that (same column) into the data rows until it finds that MAX value;
  3. Take note of the row where it found that MAX value, and return the value of the date from Column A

Make sense? I've got a formula set up with INDEX and MATCH, but while it is giving me the correct numbers for the top value and what type of analytic it is, I can't seem to get the correct date (what it looks like it's doing is counting the columns from left until it finds the value for #2, then counting that number of rows down).

Can anyone help?

Upvotes: 0

Views: 104

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

You can do this by nesting MAX, MATCH and INDEX

Mockup of your data

enter image description here

The Formula

=INDEX(Table1[Date], MATCH(MAX(Table1[#Totals]), INDEX(Table1, , MATCH(MAX(Table1[#Totals]), Table1[#Totals], 0) ), 0 ) )

Explanation of the Formula

enter image description here

Of course, you don't have to use a Table. A formula for the same data, without Structured References

=INDEX(Sheet1!$K$2:$K$10, MATCH(MAX(Sheet1!$K$11:$P$11), INDEX(Sheet1!$K$2:$P$10, , MATCH(MAX(Sheet1!$K$11:$P$11), Sheet1!$K$11:$P$11, 0) ), 0 ) )

Upvotes: 1

Related Questions