data updating based on values on another worksheet

[acerlaptop]

Hey guys, im using index on this waorksheet..
SAMPLE FILE.xlsx

what I want is to automatically "Data breakdown" sheet when I enter value on "Summary" worksheet in "Kind" part.
But what happens is that my formula only finds the first "Kind" in the "Data" worksheet.

Any idea how to fix this?? thanks

[zbor]

Here, try this:

I've added first column with row number so each cell don't need calculate that value again but just take it from there.
It's possible to have solution iwthout that column but this is better approach.

[acerlaptop]

Here, try this:

I've added first column with row number so each cell don't need calculate that value again but just take it from there.
It's possible to have solution iwthout that column but this is better approach.

thanks for the help..

but is there a way to do this without the use of another column???

[acerlaptop]

Here, try this:

I've added first column with row number so each cell don't need calculate that value again but just take it from there.
It's possible to have solution iwthout that column but this is better approach.

i cant make it work

[zbor]

Sure. Here it is:

[acerlaptop]

tnx.. its working.. but how do i put {} in a formula???

[zbor]

It's an array formula.

It must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You can not enter those brackets manually.

[acerlaptop]

It's an array formula.

It must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You can not enter those brackets manually.

thanks for the help..

can i ask you another question??? what if i used multiple criterias by using and()?
i can't seem to make that work..

Thanks

[zbor]

I would really suggest you to go with first solution.
Then you can easier control multiple criteria and rest of the table (column B onwards) is always same formula.

Also, why don't you just put filter in first row then you can filter out by multiple criteria in multiple columns.

[acerlaptop]

I would really suggest you to go with first solution.
Then you can easier control multiple criteria and rest of the table (column B onwards) is always same formula.

Also, why don't you just put filter in first row then you can filter out by multiple criteria in multiple columns.

well filtering is always the easy way. however my data consists of almost 1 million rows. even if i filter it, the bulk of all the data in the workbook remains. that's why im looking for a way top only include in the worksheet only those who passes all the criterias. then ill just have to move the data sheet in another workbook. right??

anyway, thanks for the help.. its working.. but one problem though, the formula itself seems to make my worksheet unresponsive.

[zbor]

Yes.
And you will do that only once.
In second approach same thing is calculated in every column so on million of data it make it slow.