Nested if Formula

[Manny Singh]

Hi All,

Could you please help to write a formula for the attached sheet, let me know if any query.

Cheers, Manny

 

[bosco_yip]

Try…….

In F3 array formula (confirm entered with SHIFT+CTRL+ENTER) :

=IFERROR(INDEX(C3:C9,MATCH(1,(--TRIM(LEFT(C3:C9,2))<=E3)*(--RIGHT(SUBSTITUTE(C3:C9,"F",""),2)>=E3),0)),"Not found")

Regards
Bosco

 

[Manny Singh]

Hi Bosco,

Thanks so much, I am still a learner in excel, could you please give me a little explanation on this formula, I will appreciate it.

Cheers, Manny.

 

[Manny Singh]

Hi Bosco,

Just need to understand the match function part.

 

[bosco_yip]

Hi Bosco,

Thanks so much, I am still a learner in excel, could you please give me a little explanation on this formula, I will appreciate it.

Cheers, Manny.

Hi Manny,

The formula is a two criteria LOOKUP using INDEX and MATCH and the format like this :

=INDEX(TableRange,MATCH(1,(CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2),0))

Regards
Bosco

 

[Manny Singh]

Thanks Bosco, if I could I will give you the title of Ninja..

 

[bosco_yip]

Thanks Bosco, if I could I will give you the title of Ninja..

Thanks Manny,

You're welcome.

Regards
Bosco