Is there a formula that returns a value from the first line matching two or more criteria? For example, “return column C from the first line where column A = x AND column B = y”. I’d like to do it without concatenating column A and column B.
True = 1, False = 0
D1 returns 0 because 0 * 1 * 8 = 0
D2 returns 9 because 1 * 1 * 9= 9
This should let you change the criteria:
I use INDEX/MATCH for this. Ex:
I have a table of data and want to return the value in column C where the value in column A is “c” and the value in column B is “h”.
I would use the following array formula:
Commit the formula by pressing Ctrl+Shift+Enter
After entering the formula, you can use Excel’s formula auditing tools to step through the evaluation to see how it calculates.
SUMPRODUCT definitely has value when the sum over multiple criteria matches is needed. But the way I read your question, you want something like
VLOOKUP that returns the first match. Try this:
For your convenience the formula in
G2 is as follows — requires array entry (
[edit: I updated the formula here but not in the screen shot]
Two things to note:
SUMPRODUCTwon’t work if the result type is not numeric
SUMPRODUCTwill return the
SUMof results matching the criteria, not the first match (as
Apparently you can use the SUMPRODUCT function.
Actually, I think what he is asking is typical multiple results display option in excel. It can be done using Small, and row function in arrays.
This display all the results that matches the different criteria