How to get a single value from a cell-range by matching multiple columns and rows

Question

I'm struggling with this one.

Here is data from 'sheet1':

  ||  A       B       C       D       E
=========================================
1 ||  C1      C2      X1      X2      X3
.........................................
2 ||  a       b       1       2       3
3 ||  a       d       10      11      12
4 ||  c       d       4       5       6
5 ||  c       f       13      14      15
6 ||  e       f       7       8       9
7 ||  e       b       16      17      18

Here's data in "sheet2":

  ||  A       B       C       D  
=================================
1 ||  C1      C2      C3  |   val
.................................
2 ||  a       d       X2  |   ?
3 ||  c       f       X1  |   ?
4 ||  e       b       X3  |   ?

Note that column C in sheet2 actually has values equal to user column names in sheet1.

I simply want to match A, B and C in sheet2 with A, B and 1 in sheet1 to find values in the last column:

  ||  A       B       C       D  
=================================
1 ||  C1      C2      C3  |   val
.................................
2 ||  a       d       X2  |   11
3 ||  c       f       X1  |   13
4 ||  e       b       X3  |   18

I've been playing with OFFSET() and MATCH() but can't seem to lock down on one cell using multiple search criteria. Can someone help please?

zolley zolley · Accepted Answer · 2014-04-30T11:22:28

I would use this function in sheet2 D2 field:

=index(filter(sheet1!C:E,sheet1!A:A=A2,sheet1!B:B=B2),1,match(C2,sheet1!$C$1:$E$1,0))

Explanation:

There is a FILTER function which will result the X1,X2,X3 values (C,D,E columns of sheet1) of the row which matches to the these two conditions:

C1 is "a"
C2 is "d"

So it will give back an array: [10,11,12] - which is the values of the X1, X2, X3 (C,D,E ) columns of sheet1 in the appropriate row.

Then, the INDEX function will grab this array. Now we only need to determine which value to pick. The MATCH function will do this computation as it tries to find the third condition C3 (which is in this case "X2) in the header row of sheet1. And in this example it will give back "2" as X2 is in the 2nd position of sheet1!c1:e1

So the INDEX function will give back the 2nd element of this array:[10,11,12], which is 11, the desired value.

Hope this helps.

How to get a single value from a cell-range by matching multiple columns and rows

1 Answers