Lookup/index "id_number" and adjustable range (dynamic)

[Jhon Mustofa]

hi all,

i have following excel problem about lookup/index data with selected cell/data (in cell K3 (id_number) as selected key) then/with dynamic range combination (adjustable range with fill data display)..

More detail information please, see my sample workbook...

for your helping, thanks in advance...

john...

[RobertMika]

=IFERROR(INDEX(A$6:A$12,SMALL(IF($B$6:$B$12=$K$3,ROW($B$6:$B$12)-ROW($B$6)+1),ROWS($B$6:B6))),"")
Confirm Control+Shift+Enter
Drag down and accross

[Jhon Mustofa]

thanks, Mika...

but where is your formula placing???

[Jhon Mustofa]

sorry, Mika..it's not my goal...
i means in cell K3 (selected data) some like drop down list when i type/click "id_number" the data is shown base on source and adjustment with fill range it...
for simple it's work like filter menus...

any ideas>>

[Fotis1991]

John.

Robert's formula does exactly what you need. Which is your problem?

[RobertMika]

Thank you Fotis.

I hope that the attached will explain everything.

[Jhon Mustofa]

thanks Fotis, It's excellent work!!!!

but i have problem, with line row "TOTAL" ( in cell K9)..how do make, nothing interval blank row between last show data with line row "TOTAL" (adjustable)

i hope you could help me....please...

[RobertMika]

lookup_john.xlsx
Please see attached but I woudl add the Total in the first row above the "$cost" line.

[Jhon Mustofa]

Robert, are you there...sorry perhaps long time i review and testing your formula and
your formula is working well and it's i wanted..

but having little problem while add data source more than 3 data (row), the result of total not showing ...
my question is ;
1) how to edit/adding data sources array more than 3 data (3 row) and showing total result..
2) it's possible to configure/modify, there is nothing blank row between the last data with string "total"..
sorry, i confused and my friend high stress to breakthrough....

i do believe one all of you, could help me!!!

one again, thanks in advance and much appreciated...

regards..

jhon m. Robert, are you there...sorry perhaps long time i review and testing your formula and
your formula is working well and it's i wanted..

but having little problem while add data source more than 3 data (row), the result of total not showing ...
my question is ;
1) how to edit/adding data sources array more than 3 data (3 row) and showing total result..
2) it's possible to configure/modify, there is nothing blank row between the last data with string "total"..
sorry, i confused and my friend high stress to breakthrough....

i do believe one all of you, could help me!!!

one again, thanks in advance and much appreciated...

regards..

jhon m.

[RobertMika]

Please see attached.
Does this help:
lookup_john(1).xlsx

[Jhon Mustofa]

Thanks, Robert,but for column cost$ (summing) still not perfect...should be total cost$ (sum) is the same line row with string "total"

[RobertMika]

N6:
=IF(AND($N$5="cost$",$N5="",K6="Total"),SUM($N$5:$N5),IFERROR(INDEX(D$6:D$1002,SMALL(IF($B$6:$B$1002=$K$3,ROW($B$6:$B$1002)-ROW($B$6)+1),ROWS($B$6:E6))),""))
Confirm COntrl+SHift+enter
Drag down

[Jhon Mustofa]

RobertMika, It's amazing!!!!!

100% perfectly work.............

many thanks!!!! i am glad now...

[RobertMika]

You are welcome.
Thanks for feedback