Here are two solutions, corresponding to my suggestions in comments on the question. For both of these, I'm assuming A1:C30 contains the data from the question.
Using a Database Function
The first solutions uses Excel's database functions. The database functions all treat a range of cells as a database, where each row is a record, and each column is a field. The first row contains the column names. The database functions also take another range of cells as search criteria, where the first row is column names and the second row is the actual criteria. Given that, in E1:F2 (or anywhere, but that's where I've put it for these examples) put:
E F
1 Store # Date
2 414 11/9/15
That's the criteria. Then in E4 (or wherever) put =DGET(A1:C30,"Data",E1:F2)
. That uses the DGET
database function to look up a column's value given a database (the A1:C30
), a column name ("Data"
), and criteria (E1:F2
). In this case, that will result in 132
. Changing the contents of F2 to 11/2/15
will change the DGET
value to 55
, etc.
This is perhaps the cleanest way, because it's easy to extend if you have additional columns in the data and criteria you want to use to match those columns. You can also reuse parts of the criteria to do other things. For example, =DSUM(A1:C30,"Data",E1:E2)
will sum all the Data values for store 414, =DSUM(A1:C30,"Data",F1:F2)
will summ all the Data values for 11/9/15, etc. It also doesn't assume anything about the order the columns are sorted in. The downside is that if you don't use the database functions a lot (like me :-) ), you'll have to re-read the help on them every time you use them (like I did for this :-) ), so it's maybe not as maintainable.
Using Indexing and Lookup Formulas
The second way combines some of Excel's lookup formulas.
Set up a worksheet the same as above, the data from the question in A1:C30 and the criteria in E1:F2. Note that in this case, we're only going to use E2 and F2, but you can leave E1 and F1 as labels as to what's in E2:F2.
Then in E4, put:
=VLOOKUP(F2,INDEX(B:B,MATCH(E2,A:A,0),1):INDEX(C:C,MATCH(E2,A:A,1),1),2)
Breaking that down, from the inside out:
MATCH(E2,A:A,0)
That finds the first match (the 0
) in the first column (A:A
) that matches the store # in E2
. It returns the relative position, and in this case because all of the first column is the lookup array, the position will be the row number of the first occurrence of the store # in E2. With the example data, if you put this in a cell by itself, it will have a value of 7
.
INDEX(B:B,MATCH(E2,A:A,0),1)
This generates a reference to a cell in the second column (B:B
) at the row determined by the MATCH
and column 1
. If you put this in a cell by itself, the value will be the value of the referenced cell, which with the example, will be the value from B7, or 11/10/14
.
MATCH(E2,A:A,1)
This finds the last match (the 1
) in the first column that matches the store #. If put this in a cell by itself, the value will be 25
.
INDEX(C:C,MATCH(E2,A:A,1),1)
This generates a reference to a cell in the third column (C:C
) at the row determined by the MATCH
and column 1
. If you put this in a cell by itself, the value will be the value of the referenced cell, which with the example, will be the value from C25, or 132
.
INDEX(B:B,MATCH(E2,A:A,0),1):INDEX(C:C,MATCH(E2,A:A,1),1)
This combines the two INDEX
formulas to generate a reference to search for the date. With the example data, it will be B7:C25 (if you put it in a cell by itself, you'll get a #VALUE!
because it results in more than one value. If you put it in a cell by itself as an array formula, you'll get the upper-left cell's value, or 11/10/14
).
=VLOOKUP(F2,INDEX(B:B,MATCH(E2,A:A,0),1):INDEX(C:C,MATCH(E2,A:A,1),1),2)
This combines it all. It uses VLOOKUP
to look for the date from F2
, in the cell range generated by the two INDEX
formulas (B7:C25), and to return the second column (the final 2
) on the matching row.
Voilá!