SOLVED

Formula for returning values from multiple criteria from multiple columns

Occasional Contributor

Formula for returning values from multiple criteria from multiple columns

Hi all,

Any chance to use a formula to get unique result from several columns (G2:H4) based on multiple criteria (B:C)? In the "F" column are only unique records.

4 Replies

Re: Formula for returning values from multiple criteria from multiple columns

=SUMPRODUCT((B2=\$F\$2:\$F\$4)*(C2=\$G\$1:\$H\$1)*\$G\$2:\$H\$4)

=INDEX(\$G\$2:\$H\$4,MATCH(B2,\$F\$2:\$F\$4,0),MATCH(C2,\$G\$1:\$H\$1,0))

Maybe with these formulas as shown in the attached file.

Re: Formula for returning values from multiple criteria from multiple columns

Unfortunately I've got a problem with the formulas, maybe due to local language settings (but I checked twice). Could you please check on the linked xls file?

I updated the file structure. I will work with 10k rows. Big thanks!

best response confirmed by PavelDrg (Occasional Contributor)
Solution

Re: Formula for returning values from multiple criteria from multiple columns

@PavelDrg

See the attached version. The formula uses structured table references.

Re: Formula for returning values from multiple criteria from multiple columns

Big thanks Hans! That's what I needed, you helped me a lot!