Excel match and index to select value with 2 column and 1 row criteria

Question

I'm trying to write a formula that can return a cell value using INDEX and MATCH but I can't make it work. I have the following setup:

year  person   pet    friend   sale
1     jeff     scout  matt     computer
1     carole   riley  tom      mouse
2     jeff     sadi   trung    computer
2     carole   daisy  ellen    mouse

Say I have three cells:

year    2
col     person
sale    mouse
RESULT: carole  [year = 2, sale = mouse, column value from 'person']

Is there a way I can use index and match to zero in on the specific cell? I will always have a year and sale provided. However, the column (person, pet, or friend) will change so I need to find the value in that particular column where the year and mouse match the cells.

Once I get a formula to work I'm going to write a more general function, but I need help getting the INDEX/MATCH combo to work properly.

Is your data in an excel list or table? If your not sure, When you click on the data does a new tab in the ribbon called Table Tools appear? If so you do if not you don't, if you don't are you ok with converting it to one? All you have to do is click on your data and press Ctrl+L — user2140261

user2140261 user2140261 · Accepted Answer · 2013-12-05T23:09:42

You could use a combination of Index and sumProduct:

=INDEX(Table1[[person]:[friend]],SUMPRODUCT(--(Table1[year]=J1)*--(Table1[sale]=J2)*ROW(Table1[[#Data],[year]]))-1,MATCH(J3,{"person","pet","friend"},0))

This would work as i n the following example:

enter image description here

So J1 contains the year to look up, J2 contains the Sale name, and J3 is the column you want to return a value from.

Another option would to add a concatenated column to your table the has the values of year and sale:

enter image description here

you would then change the formula to:

=INDEX(Table1[[person]:[friend]],MATCH(K1&K2,Table1[Year&Sale],0),MATCH(K3,{"person","pet","friend"},0))

slightly shorter and defiantly faster.

NOTE: This might not work with versions of excel below 2010, if you are running an older version I can supply a modified answer.

Excel match and index to select value with 2 column and 1 row criteria

1 Answers