INDEX and IF formula

[Jagat Pavasia]

DEAR SIR,

I have a sheet contain some data in D5:F12
d5:d12 have value
e5:e12 have category
f5:f12 have sub-category

and then i want that
if i type "COST" in E18 then answer should be in from E19 to E31
if i type both in E18="cost" and F18="A" then answer should be also in from E19 to E31
if i blank both E18 and F18 then nothing to do and blank

for more explain i have attached 3 photos,

please give me formula that can i enter into E19 to E31
i am a new in excel

 

[jasonb75]

I think we're going to need a bit more information to work with.

 

[Jagat Pavasia]

I think we're going to need a bit more information to work with.

please download my 3 photos and then please give me solution

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F	G	H
1
2
3
4				Value	Category	Sub-Catgory
5				10	COST	A
6				20	COST	A
7				30	COST	A
8				40	COST	A
9				50	COST	B
10				60	COST	B
11				70	COST	B
12				80	COST	B
13
14
15
16
17		Enter your Category			category	sub-category
18					cost	A
19					10
20					20
21					30
22					40
23
24
25
26
27
28
29
30
31
32
33
Sheet1

 

[jasonb75]

This will work

=FILTER(D5:D12,((E5:E12=E18)+IF(F18<>"",F5:F12=F18))>0,"")

 

[Jagat Pavasia]

This will work

=FILTER(D5:D12,((E5:E12=E18)+IF(F18<>"",F5:F12=F18))>0,"")

THIS WILL WORK,
BUT,
if i type only "cost" in E18 then all entry should be displayed contain "cost"
and then
when i type "cost" in E18, "A" in F18 then only show that both cost and A match entry

 

[jasonb75]

I misread the question while I was scrolling up and down

=FILTER(D5:D12,(E5:E12=E18)*IF(F18<>"",F5:F12=F18,1),"")

 

[Jagat Pavasia]

I misread the question while I was scrolling up and down

=FILTER(D5:D12,(E5:E12=E18)*IF(F18<>"",F5:F12=F18,1),"")

it is working as i need, thank you so much

 

[Fluff]

@Jagat Pavasia if the Filter function works for you then you are not using 2019, so I would recommend updating your profile to show what you are using.

 

[Jagat Pavasia]

ok sir i will update my profile soon,

but one more question that if my both E18 and F18 empty then raw has "0".
can i remove it ?

 

[jasonb75]

Try this one.

=FILTER(D5:D15,IF(E18<>"",E5:E15=E18)*IF(F18<>"",F5:F15=F18,1),"")