• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index and Match Function

A

asowdi

New Member
Hello all

I am using below function to one my spread sheet, i almost got the solution but strucked in last bit, i need your help to solve this.


I am copying data from Cell "G" if it match with value existing in "H & I" cells. Until here everything is fine. Match will stop once it gets first result.

My requirement is How to run this Match funtion from G7 to G12 eventhough its true.


=IFERROR(INDEX((G$7:G$12),MATCH($H17,$I$7:$I$12,0)),"")
 
HI asowdi,


It is not clear from your post that what is the exact requirement. Can you explain using an example or so. Or upload a sample file. If you want to work this formula for entire G7:G12 just drag it down.


Regards,

Faseeh
 
Asowdi


Your formula is fine and works for me

So I assume your data is incompatible


Is H7 the same type of data that is in I7:I12 ?

Even if they all look like numbers, retype them all just in case


Otherwise as Faseeh said either post your data or let us know what values you have in the ranges
 
Hui and Faseeh


Thanks for your Quick response.

I have uploaded the document in below link.

I have pasted Current result and desired result in Sample sheet. I will try to explain briefly about the requirement

Engineers in our company will be assigned to particular Tasks all the week on different days. Same details will be entered in the spread sheet in columns Task name, Engineer Name, Weekdays. If engineers working on different task on different days my table should show the how many days he had occupied.

The two result showed in attached spreadsheet will give more clarity.


https://skydrive.live.com/redir.aspx?cid=3b395c3a7e1aaadf&resid=3B395C3A7E1AAADF!107&parid=undefined


Thanks in advance

Ashwin
 
Hi Ashwin,


See this file: http://dl.dropbox.com/u/60644346/Sample_Ashwin_resolved.xlsx


..and thanks for enlisting me in list of engineers :)


Regards,

Faseeh
 
Faseeh,

One more request.

I am trying to capture task handled by each engineer in once cell separated by commas. Can u please help me to get the right functions?

Sorry to bother you brother.

I have copied Sample file in below path, the cells in green color will show the requirement.


https://skydrive.live.com/redir.aspx?cid=3b395c3a7e1aaadf&resid=3B395C3A7E1AAADF!110&parid=undefined


Regards

Ashwin
 
Hi Ashwin,


Thanks for the compliment and regarding your last post, let me try that... :)


Regards,

Faseeh
 
Hi Ashwin,


It is difficult to be done like you have done i.e. multiple tasks in one cell, however if you accept one task per cell then following formula will do the work (enter in H13 in your sheet with ctrl+shift+enter):

[pre]
Code: 
=IFERROR(INDEX($A$3:$A$8,SMALL(IF($B$3:$B$8=$B13,ROW($B$3:$B$8)),COLUMN(A1))-2,0),"")
[/pre]
...drag down and across to get all tasks.


Regards,

Faseeh
 
Hi Ashwin / Faseeh ,


I was just going through the worksheet and I think there is some issue on the MATCH ; check out Ashwin's workload on Monday and Hui's on Friday.


Narayan
 
Narayan


Thanks for your time on this.

I have posted old sheet over here with different requirement. My earlier requirement code given by Faseeh and its working fine.

My other requirement is below,Request you to ignore my earlier need in this chain. Below is latest.

"I am trying to capture task handled by each engineer in once cell separated by commas. Can u please help me to get the right functions?"

Thank you very much. Have good Weekend.


Regards

Ashwin S
 
@Ashwin,


You are welcome!


@Narayan, Hi. Ashwin addressed the last post by you... I think what he is asking might be accomplished by a macro. If it could be by a formula then it will be a good lesson for me.


Thanks & Regards,

Faseeh
 
Hi Faseeh,


I was wondering can we use sumifs instead of sumproduct for the first spreadsheet you attached ? Or the result will be different ?


http://www.mediafire.com/view/?la4kluffq8l9als


Please refer to the link.


Regards,


Z.
 
@ mrzoogle,


That's brilliant actually, a compacter and speedier formula!! :) (Sumif(s) is faster then sumproduct())...


Thanks for this quick lesson..


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top