Please help with lookup / index formula where there are multiple instances of the search value

Janiece Denissen

Hi All, I am new Smartsheeter and having difficulty in finding the correct lookup / index formula where there are multiple instances of the search value. I have tried a formula which works in excel (Index / Small / Row), but "ROW" is not recognized in Smartsheet.

In a nutshell, I have a master sheet with a list of clients, who are assigned assigned a project manager (PM) to look after them, and the PM will be looking after 10+ clients. I need to pull data from a number of columns in the mast sheet to a separate sheet based on the PM's name, so that I can have a dedicated sheet for each PM with his list of clients, and the costs associated with them. I have provided a sample image to create a better understanding. I did try a report, but I can't work with the figures to sum total and create an approval workflow.

Please could someone point me in the right direction for the formula to use?

Extract from Master Sheet

Desired Outcome in PM Sheet

David Joyeuse

Hi @Janiece Denissen

Considering what you want to do, the report is definately the best way to do it.

If the problem is that you can't sum values in the report, there's some workaround.

Just create a second sheet, with the EXACT name columns as your first sheet. (Here: PM, Complex Name, Fee and %). Watchout for Mins and Maj as it is case sensitive.

Then use these formulas in the Fee and % columns.

=SUMIFS({1st Sheet Fee Range}, {1st Sheet PM Range}, [PM]@row)

=SUMIFS({1st Sheet % Range}, {1st Sheet PM Range}, [PM]@row)

You may also want to put TOTAL in the Complex name Column next to each PM if you want (You may have to add some characters to sort the column as desired though).

Then add this sheet to the Report you want to display.

Smartsheet will treat all your columns with the same names as the same column and you'll get the desired effect.

Hope it helped!

Janiece Denissen

Hi David Joyeuse 

Thank you very much for your help. I have gone with your solution and set up an approval workflow against your recommended summary sheet where alll the PM totals are listed which is activated when the report is attached. I wasn't aware that the report will just add the additional informaton to the last row of the report, its quite a handy tip, so thank you for that too.

Regards

Janiece