Forum Discussion

Microsoft

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...

Test Sheet.xlsx12 KB

excel

Formulas and Functions

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.

mathetes

Silver Contributor

Jul 04, 2022

dpenhadavid

Here's a solution. I consolidated your two databases--no need for two sheets for Internal vs External; just differentiate by a column showing whether it's an internal or external. Makes the formula simpler.

=IFERROR(IF(MATCH(A2,'Offer Sheet'!$B$2:$B$20,0)>0,"Closed"),"Open")

Test Sheet_Rev1.xlsx12 KB

dpenhadavid
Microsoft
Jul 04, 2022
thanks mathetes

However, I cannot have the sheets for Internal & External separate as they are managed by 2 separate teams. Would it be possible to help me design a formula for 2 separate sheets?

Thanks.
- Riny_van_Eekelen
  Platinum Contributor
  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.
  - dpenhadavid
    Microsoft
    Jul 04, 2022
    this is amazing! Thank you so much!