CODEWITHSUNDEEP
excelindexingmatch
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

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

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.

Upvotes: 0

Views: 14109

Answers (1)

user2140261
user2140261

Reputation: 7993

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.

Upvotes: 2

Related Questions