{"id":1562,"date":"2015-08-18T18:24:02","date_gmt":"2015-08-18T16:24:02","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1562"},"modified":"2024-06-06T20:49:51","modified_gmt":"2024-06-06T18:49:51","slug":"index-match-2-criteria-with-data-validation-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/index-match-2-criteria-with-data-validation-in-excel\/","title":{"rendered":"Index Match 2 Criteria with Data Validation"},"content":{"rendered":"

 <\/p>\n

What does it do? <\/strong><\/em><\/p>\n

Searches the row position of a value\/text in one column (using the MATCH function)\u00a0and returns the value\/text in the same row position from another column to the left or right (using the INDEX function)<\/p>\n

Formula breakdown: <\/strong><\/em><\/p>\n

=INDEX(array<\/span>,\u00a0MATCH(lookup_value, lookup_array, [match_type]<\/span>)<\/p>\n

What it means: <\/strong><\/em><\/p>\n

=INDEX(return the value\/text<\/span>,\u00a0MATCH(from the row position of this value\/text)<\/span>)<\/p>\n

\n

We can use the INDEX-MATCH<\/strong> formula and combine it with Data Validation<\/a> drop down menus to return a value based on 2 criteria.<\/p>\n

This is a little advanced so you will need to drop what you are doing and really focus.\u00a0 Let’s go…<\/p>\n

First we need to convert our data into an Excel Table by pressing Ctrl+T<\/p>\n

See tutorial on how to convert to an Excel Table here<\/a><\/span><\/p>\n

We then create drop down menus for our Sales Rep<\/em> column and another one for our Units\/Sales\/Avg Sale<\/em> column names<\/p>\n

See tutorial on how to insert drop down menus here<\/a><\/span><\/p>\n

Once the above are done we need to create our formula.<\/p>\n


\n