CODEWITHSUNDEEP
excelexcel-formula
S.C
S.C

Reputation: 21

INDEX MATCH Formula in Excel

I recently took over this spreadsheet from someone who left the company. Formula in T column:

=INDEX(BFTable, MATCH(1, (UPPER(LEFT(ST, 2))=$E$1:$E$315)*
      (ProcessingDate>=$A$1:$A$315)*(ProcessingDate<=$B$1:$B$315)*
      (EffectiveDate>=$C$1:$C$315)*(EffectiveDate<=$D$1:$D$315)*
      ($AI3=$P$1:$P$315)*($F$1:$F$315="HM"),0),COLUMN()-COLUMN($T$2)+1)

Could someone explain to me what this formula is trying to do? What is ST? The output is the same with processing date in A column. There is no BFTable, although the tab name is BF.

Thanks,

SC

Upvotes: 2

Views: 762

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

The pieces BFTable and ST are probably named ranges.

What the formula does is look up in the range BFTable the row associated with the MATCH(...) part and the column given by COLUMN()-COLUMN($T$2)+1.

The MATCH section is a long conditional. It searches for the first case where all of the following conditions are true and returns the index of that row:

UPPER(LEFT(ST, 2))=$E$1:$E$315
ProcessingDate>=$A$1:$A$315
ProcessingDate<=$B$1:$B$315
EffectiveDate>=$C$1:$C$315
EffectiveDate<=$D$1:$D$315
$AI3=$P$1:$P$315
$F$1:$F$315="HM"

If they all return TRUE, then multiplying them together results in 1, but if any of them returns FALSE, then multiplying them results in 0, which won't match to 1 (the first argument of the MATCH function).

Upvotes: 6

Related Questions