Help with a formula based on conditions

VaneT

Hello,

I need assistance with creating a formula for the following:

1. In the Annual $ cell, I would like the salary to automatically appear when two conditions have been met: the user provided the region (either US, CEMEA, LATAM, ASIA) AND the user has provided the Job Level.
2. The salary should come from a different sheet (I have titled it "Reference" sheet).

First sheet:

Reference Sheet:

How can I bring the salary to the first sheet based on the region provided and the job level? Does somebody have a formula to meet this need? ex. someone at the USA in a 32-Sr Manager level would make $42, 254.

THANK YOU

Lachlan Stead

There are a few options but it may be faster to move from a matrix to a listing - eg. level "60 - VP" would end up with 4 relevant lines. You could leave as single column with a combined description or have two columns and then a hidden column on each sheet with the role and region combined... your lookup would then be based on the hidden column value.

Not sure if that helps?

Lachlan Stead

There are a few options but it may be faster to move from a matrix to a listing - eg. level "60 - VP" would end up with 4 relevant lines. You could leave as single column with a combined description or have two columns and then a hidden column on each sheet with the role and region combined... your lookup would then be based on the hidden column value.

Not sure if that helps?

VaneT

@Lachlan Stead Thank you! I used the index collect formula and formatted my columns as you suggested. It worked.

Lachlan Stead

Good to hear!