Multiple criteria in a row for VLOOKUP

Eric Taylor

I am trying to create a report from data with multiple criteria in a row. The objective is to return the residual risk value for each combination of risk + strategic initiative. In Excel, I was able to accomplish what I am looking for with this formula (=VLOOKUP(CONCATENATE(D$2,"-",$B36),'FY 2020 ERM Assessment'!$A$11:$W$410,17,FALSE). In the table, I had to create a 'helper' field (=[@Initiative]&"-"&[@Risk]) to enable this formula to work. 

 

In Smartsheet, I can create the helper field, but there doesn't appear to be the ability to use the CONCATENATE function. Does anyone know a workaround? 

 

Thank you, 

 

 - Eric 

Paul Newcome

You can use either

 

=JOIN(range_to_join, [delimiter])

 

or

 

=[Column A]1 + "-" + [Column B]1

L_123

You can use the join function or even just add text together to concatenate in smartsheet. That said, I personally prefer to just use the collect formula as it tends to be much more straightforward.

=index(Collect(),) will return what you want.

https://help.smartsheet.com/function/join

https://help.smartsheet.com/function/collect

https://help.smartsheet.com/function/index

 

Side note, Vlookup is not optimal for smartsheet or excel. I recommend reading this article on why to use index match:http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/

 

 

Eric Taylor

Thank you, Paul! That did the trick!

Paul Newcome

Happy to help! 

JeremiahHorstick

Keep in mind that while using the index(collect()) method if 2 rows meet all criteria only the first is returned.