Use Excel to perform multi-criteria searches

Excel multi-condition return value

Array formula:

=IF(SUMPRODUCT((A:A=＂P349＂)*(B:B=＂＂))>=1,＂＂,TEXT(MAX((A:A=＂P349＂)*B: B),"yyyy-m-d"))

After inputting, do not press Enter directly, press

Ctrl Shift Enter

Finish.

The actual measurement is valid and can automatically determine the value of column B which is P349 in column A.

If it is Excel 2003, please change the formula to:

=IF(SUMPRODUCT((A1:A1000=＂P349＂)*(B1:B1000=＂＂))>=1,＂＂,TEXT(MAX((A1:A1000=＂P349＂)*B1: B1000),"yyyy-m-d"))

Same as Ctrl Shift Enter to end the formula.

Because Excel 2003 does not support entire column arrays.

If you don’t understand something, you can ask...

excel multi-condition search return value

1

2

3

4

5

6

7

FunctionSpecialSum(Str1 AsString, Val1 AsSingle, Rng1 AsRange)

IfVal1 > WorksheetFunction.VLookup(Str1, Rng1, 3, False) Then

SpecialSum = Val1 * WorksheetFunction.VLookup(Str1, Rng1, 2, False)

Else

SpecialSum = WorksheetFunction.VLookup(Str1, Rng1, 4, False)

EndIf

EndFunction

'Put it into the module and you can use it

Save you the trouble of writing a bunch of formulas

The above is the detailed content of Use Excel to perform multi-criteria searches. For more information, please follow other related articles on the PHP Chinese website!