Any other formula to replace (V Lookup)

[rush2rajen]

Hi Excel Gurus
Please find the attached file.
I had manually written the solution in yellow colour mark. I had tried several times to write a formula but it all went in vain.

For eg. On Sunday HK1,HK2,HK3 will work in Room No 1 for 120 mins each.

Similarly I want to write a formula which will give me a result for HK1, HK2, HK3 and in which Room they will work and for how many mins and on which days.

Request you to kindly help on this.
Thanks in Advance


Regards
Raj.

 

[Deepak]

rush2rajen


It's little bulky but working.


For dragging, First select any two corresponded col like as C13 to D15 & then drag it right.

 

[rush2rajen]

Hi Deepakji,

Thanks a lot. But can't it be made little simple. I think its an array formula Ctrl+Shift+Enter.

Thanks
Raj

 

[rush2rajen]

Hi Excel Masters,

I am trying my level best to write this formula as per my selection but I am getting it wrong.

Can anyone please help me on this. Can i be done without array formula.

Regards
Raj

 

[bosco_yip]

Try,

1] C8, enter formula and copy down :

=IFERROR(INDEX($B$2:$B$5,MATCH("*"&REPLACE($B8,3,," ")&"*",INDEX($F$2:$L$5,0,MATCH(C$7,$F$1:$L$1,0)),0)),"")

2] D8, enter formula and copydown :

=IF(C8="","",VLOOKUP(C8,$B$2:$C$5,2,0))

3] Select range C8:D10 together, copy across right.

Regards
Bosco

 

[rush2rajen]

Thanks a lot Bosco.
I just want to understand what does REPLACE($B57,3,," ") means what does 3 stands for. If you don't mind can you please clarify so that I had a better understanding of the same while using it afterwards.

Thanks
Raj

 

[bosco_yip]

Thanks a lot Bosco.
I just want to understand what does REPLACE($B57,3,," ") means what does 3 stands for. If you don't mind can you please clarify so that I had a better understanding of the same while using it afterwards.

Thanks
Raj

Criteria Cell e.g. B8 : HK1

but,

Source Table e.g. I3 : HK 1

So,

REPLACE($B8,3,," ") >> make HK1 become HK 1

Regards
Bosco

 

[rush2rajen]

Thanks Bosco.

Just one more question if Source Table e.g. I3 would have been HK1 and Criteria Cell e.g. B8 : HK1 then do i need to use the Replace formula or it will have some another formula.

or if Source Table e.g. I3 would have been HK 1 and Criteria Cell e.g. B8 : HK 1 what will be the formula


Thanks
Raj

 

[bosco_yip]

Thanks Bosco.

Just one more question if Source Table e.g. I3 would have been HK1 and Criteria Cell e.g. B8 : HK1 then do i need to use the Replace formula or it will have some another formula.

or if Source Table e.g. I3 would have been HK 1 and Criteria Cell e.g. B8 : HK 1 what will be the formula

Thanks
Raj

If the Criteria data and Source Table data are in same style,

REPLACE function is not required, and formula become like this :

=IFERROR(INDEX($B$2:$B$5,MATCH("*"&$B8&"*",INDEX($F$2:$L$5,0,MATCH(C$7,$F$1:$L$1,0)),0)),"")

Regards
Bosco