Finding exact value with many criterions using joined index, match and sumifs functions

Bieksha

Hi,

everyday I'm working with lot data, there is many of columns and even more of rows. And there is no specific function for finding text when several criteria should be used.

For finding exact value we can use index and match function. For finding number we can use sumifs function with many of criteria.

I had problem with finding exact value with lot data so make simple trick with joining sumifs, index and match functions.

Problem is that sumifs function can sum only numbers, so I used only one additional column with unique numbers on it. In that case is possible to use many of criteria ranges and receiving one unique value and after that index and match function should be used to get desired text by searching that unique number.

=IFERROR(INDEX($[0]$1:$[1]$179; MATCH(SUMIFS($Speisorius$1:$Speisorius$179; $[Speisorius, mm]$1:$[Speisorius, mm]$179; [Speisorius, mm]184; $Stiklai$1:$Stiklai$179; Stiklai184); $Speisorius$1:$Speisorius$179; 0); MATCH($[Paketo formulė]$180; $[0]$1:$[1]$1; 0)); "")

 

Using that simple formula I received unique text by searching in table with more then 20 columns and 179 rows. As many as needed criterion ranges and criterions could be used

 

Shaine Greenwood

Hi Bieksha,

This is an amazing way to find multiple criteria. Out of curiosity, is there a function that you'd like to have in Smartsheet that makes this easier for you?

If that's the case, please submit a Product Enhancement Request and let our Product team know what function you're wanting in Smartsheet.

Thanks for sharing your process with the community!

Shaine Greenwood

Hi Bieksha,

This is an amazing way to find multiple criteria. Out of curiosity, is there a function that you'd like to have in Smartsheet that makes this easier for you?

If that's the case, please submit a Product Enhancement Request and let our Product team know what function you're wanting in Smartsheet.

Thanks for sharing your process with the community!