How to use formula to search for result that satisfy 3 criteria

[Eric Tsang]

Dear all

I want to know how i can get the result that would satisfy 3 criterias

I have a data base that categorized into city, company, service and price

I would like to get the price based on city that user choose
Then, the city filter the company that can select
Once they got the company,service then can be selected

finally, the price of the services

See attached excel file

Thanks
Eric

[Glenn Kennedy]

Delete the helper column and use this (see sheet as this refers to the sample MINUS helper column).

=INDEX($G$5:$G$12,MATCH(1,INDEX(($D$5:$D$12=I5)*($E$5:$E$12=J5)*($F$5:$F$12=K5),0),0))

[Phuocam]

Or try:

=LOOKUP(2,1/($D$5:$D$12=J5)/($E$5:$E$12=K5)/($F$5:$F$12=L5),$H$5:$H$12)

[Eric Tsang]

Dear all

Thanks so much

I use a helper column that =CONCAT(J6,K6,L6)as in M5

=INDEX($H$5:$H$12,MATCH($M5,$G$5:$G$12,0))

It seems better

I get the idea from https://www.youtube.com/watch?v=zjfpA2kJhm8&t=261s

Thanks

Eric

[Glenn Kennedy]

Why useit, if there is no need? formulae with concatenation also have a reputation of being a bit slow.

Anyhow.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Glenn Kennedy]

You can use a helper, of course, but the problem is relatively simple to solve without a helper. Most people want to avoid helpers!!! Even in circumstances where they are really helpful.

=INDEX($H$5:$H$12,MATCH(J5&K5&L5,$G$5:$G$12,0))

will work in your case.

[CARACALLA]

	D	E	F	G	H	I	J	K	L
2		DataBase
3
4	City	Company	Service	Costs		City	Company	Service	Costs
5	BJS	A	Transfer	10		SIA	B	Coach	19
6	BJS	A	Tour	11
7	BJS	A	Meal	12
8	BJS	A	Acc	13
9	BJS	B	Transfer	15
10	BJS	B	Tour	17
11	SIA	B	Coach	19
12	SIA	B	Acc	20

L5=SUMIFS(G5:G12,D5:D12,I5,E5:E12,J5,F5:F12,K5)

[Eric Tsang]

Glenn

I try to apply to the real case, it does not work.

Can you enlighten me what wrong?!

Eric

[Glenn Kennedy]

I don't know which one you were trying to replicate!! But see the file attached.

[Eric Tsang]

Caracalla

Thanks, i never think of the Sumifs to work like this

But in real case, the Costs has variety items not just 1 cost

see the attached, i though the Index and Match would be a good choice

Eric

[AliGW]

You have marked the thread as solved - is it?

[Eric Tsang]

Glenn

I like your solution but when i try to change the service, the Price did not change.

Did i did something wrong again?

Eric

[Eric Tsang]

Ali

Sorry, as Glenn has given a very good advice of not to use the Helper.

I am eager to learn more

Thanks for your understanding

Eric

[AliGW]

Then please remove the solved tag until you have learnt what you need to know.

[Glenn Kennedy]

How can two services have the same code?? That does not make sense...

[Eric Tsang]

Glenn

Same code as Transfer
But the service is different, go to different airport in the same city

Airport transfer - Captial Airport
Airport transfer - Daxing Airport

Eric

[Glenn Kennedy]

I deleted your VLOOKUP. It will always return the FIRST matching result (i.e. to Capital airport).

[Eric Tsang]

Dear Glenn

Thanks so much ..

Why and what mean by Match(1,...what is this 1 mean?

Thanks
Eric

[CARACALLA]

V4=IF($A4<>"",SUMPRODUCT(($A$4:$A$100=$P4)*($B$4:$B$100=$Q4)*($C$4:$C$100=$R4)*($D$4:$D$100=$S4)*($E$4:$E$100=$T4)*($F$3:$M$3=$U4)*($F$4:$M$100)),"")

copy down

[Eric Tsang]

Caracalla

Thanks. I perfer Glenn 's solution.

Appreciated

Eric

[Glenn Kennedy]

1 means .... 1 !!

Eaxh of the ($A$4:$A$5=P4) bits resolves to TRUE or FALSE. When you start multiplying these together you get 1 or zero. There is only one correct result, so the formula returns an array of zeros and a singe 1. The MATCH returns the row number of the only value that returns a 1 (ie when all criteria have been met).

[Eric Tsang]

Glenn

Thanks
Eric

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.