IF Formula... Urgent Help Required.

Hello Friends

I have been working on some project that requires heavy data entries. To save time, I was thinking about applying the conditional formula (IF) but unfortunately its way beyond my skills and capacity of using excel.

I will try to recreate the scenario for you to better understand and recommend your solution.

Project Part:

Company is analyzing the hiring that has been done in the past and it needs to evaluate who was holding which position at the time of specific appointment. The table we are using looks similar to the table given below:


REQUIREMENT:

I want such a formula that picks the name of CEO and fill the question mark part from the incumbency table similar to the one created below:


I would really appreciate if someone could suggest the formula for it.

Thank you.

Try VLOOKUP.

e.g. =VLOOKUP(A2,$X$1:$Y$10,2,0)

where A2 contains your S# and X1:Y10 contains first two columns of your lookup table... this will find S# in column X and return CEO from column Y

NBVC said:

Try VLOOKUP.

e.g. =VLOOKUP(A2,$X$1:$Y$10,2,0)

where A2 contains your S# and X1:Y10 contains first two columns of your lookup table... this will find S# in column X and return CEO from column Y

Click to expand...

Thanks for the reply.
But you misunderstood the question i am assuming as the "VLOOKUP" will recall the name of CEO if the exact date matches with the date of appointment and not the dates in between of the period of specific CEO.

So do we need to match the S# also, as I see they are all unique?

NBVC said:

So do we need to match the S# also, as I see they are all unique?

Click to expand...

Serial # has nothing to do with the desired result.

Let me make it more clear...
A guy was appointed on 15.04.2012.
And i want to extract the name of CEO on 15.04.2012.

The struggle i have to go through is to manually check whose period was 15.04.2012 and manually enter the name.

Like in this above stated random appointment date... the name of the CEO would be Henry as he held the charge of CEO between 04.02.2011 to 17.10.2013.

I am hoping that i have provided sufficient information with clarity.

If you think there is still some ambiguity, do let me know.

Thanks

=INDEX($X$2:$X$10,MATCH(1,($Y$2:$Y$10<=B2)*($Z$2:$Z$10>=B2),0))

confirmed with CTRL+SHIFT+ENTER not just ENTER

where X2:Z10 contains table starting with CEOs in column X, and B2 contains the Date of Appointment to use to find match.