I have a document with datas and I want to automatize a research.
for exemple I have
| lastName | firstName | age | result And I'd like to get the result row where lastName = Smith, firstName = Jhon and age = 42
I know how to do that with loops but the complexity is hight is there an other solution ?
You can try This array formula:
=INDEX(D2:D10;MATCH(1;(A2:A10="Smith")*(B2:B10="John")*(C2:C11=42);0)) This is an array formula so you need Ctrl Shift Enter to enter the formula
CRondao has a good solution that will return the first match, and works on any data type. If result is numeric and you want to sum up all matches, this will work:
=SUMPRODUCT(--($A$1:$A$4="Smith"),--($B$1:$B$4="Jhon"),--($C$1:$C$4=42),$D$1:$D$4) Note, this is not an array formula.
In cases like this, I usually build a "lookup key" by using CONCATENATE() to combine the values. I can then return results using VLOOKUP() or SUMIF() as required since I find those functions intuitve and easier to write than range offset/index/match formulas.
Here's an image of a demo worksheet, upper left cell is "A1":
If there are multiple matches, you'll see the total count immediately. You can also use the Auto-Filter, etc., etc....
Here are the formulas in the filter row:
A3: =CONCATENATE(B3,C3,D3) E3: =SUMIF($A$5:$A$10,$A$3,E$5:E$10) F3: =VLOOKUP($A$3,$A$5:$F$10,6,FALSE) Since F3 is using VLOOKUP() with option FALSE, it will only return the first result it finds in the list, otherwise it returns #N/A.
These are the formulas in the first row of data, which can be copied down to cover the entire range:
A6: =CONCATENATE(B6,C6,D6) E6: =IF($A6=$A$3,1,"") F6: =IF($A6=$A$3,ROW(),"") 
resultnumeric, and if so, will the other columns be unique (no chance of duplication), or if duplication is possible, a sum of the corresponding values is the desired return result?