Forum Discussion

dpenhadavid's avatar
dpenhadavid
Icon for Microsoft rankMicrosoft
Jul 04, 2022
Solved

Need help with Excel Formula

I have an interesting problem with excel sheet. [Refer to attached file] I am trying to input a validation/formula in 'Sheet 3 - Hiring Planning - column B' - such that if the Position Number (Sh...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jul 04, 2022

    dpenhadavid Perhaps this does what you need:

     

    =IF(IFERROR(MATCH(A2,'Offer Sheet - External'!B:B,0),0)+IFERROR(MATCH(A2,'Offer Sheet - Internal'!B:B,0),0),"Closed","Open")

     

    This formula tries to match A2 in both lists. It returns a number or #NA. IFERROR captures the #NA errors and sets the value to 0. If the sum of the two is anything other than 0, it means that the Position Number was found in either of the lists (External or Internal), and will return Closed. Otherwise Open. 

Nikkie1977's avatar
Nikkie1977
Copper Contributor
Jul 11, 2022

dpenhadavid 

Hi, 

I really need help with this excel formula please. I am creating a Risk Matrix so basically in a dropdown you pick the Likelihood and in another dropdown you pick the Consequence and the result should be the Low, Medium or high in the same colours.

 

Can you please help me with this?

 

 

Resources