Frequency Comparison Formula

[yessir]

Im looking for a formula to tell me which company has a higher frequency at the same location and disregards the information if the start location is unique to that company.

(C1 stands for Company 1)

A​	B​
C1
Start location	Frequency
EEE	1
AAA	2
FFF	2
BBB	2
CCC	6
GGG	2
DDD	2
HHH	1
III	1
JJJ	1

(C2 stands for Company 2)

D​	E​
C2​
Start location	Frequency
LLL	7
AAA	8
MMM	5
BBB	3
CCC	2
NNN	2
OOO	7
DDD	1
PPP	1
QQQ	2

For example: it would look like this
Strat Location	Frequency
AAA	C2 Higher
BBB	C2 Higher
CCC	C1 Higher
DDD	C1 Higher

 

[lrobbo314]

How about this?

Book1
	A	B	C	D	E
1	A	B		D	E
2	C1			C2
3	Start location	Frequency		Start location	Frequency
4	EEE	1	NA	LLL	7
5	AAA	2	C2 Higher	AAA	8
6	FFF	2	NA	MMM	5
7	BBB	2	C2 Higher	BBB	3
8	CCC	6	C1 Higher	CCC	2
9	GGG	2	NA	NNN	2
10	DDD	2	C1 Higher	OOO	7
11	HHH	1	NA	DDD	1
12	III	1	NA	PPP	1
13	JJJ	1	NA	QQQ	2
Sheet2

Cell Formulas
Range	Formula
C4:C13	C4	=IF(ISNUMBER(MATCH(A4,$D$4:$D$13,0)),IF(INDEX($E$4:$E$13,MATCH(A4,$D$4:$D$13,0))>B4,"C2 Higher","C1 Higher"),"NA")

 

[yessir]

Yes that worked beautifully. However, if the frequency for both are the same is it possible to input into the function somewhere that would say "Same" if the frequency was the same?

 

[lrobbo314]

How about this?

Book1
	A	B	C	D	E
1	A	B		D	E
2	C1			C2
3	Start location	Frequency		Start location	Frequency
4	EEE	1	#N/A	LLL	7
5	AAA	2	Equal	AAA	2
6	FFF	2	#N/A	MMM	5
7	BBB	2	C2 Higher	BBB	3
8	CCC	6	C1 Higher	CCC	2
9	GGG	2	#N/A	NNN	2
10	DDD	2	C1 Higher	OOO	7
11	HHH	1	#N/A	DDD	1
12	III	1	#N/A	PPP	1
13	JJJ	1	#N/A	QQQ	2
Sheet2

Cell Formulas
Range	Formula
C4:C13	C4	=LOOKUP(B4/INDEX($E$4:$E$13,MATCH(A4,$D$4:$D$13,0)),{0,1,2},{"C2 Higher","Equal","C1 Higher"})

 

[yessir]

Yes, thank you for your help.

 

[yessir]

I am having some trouble with the formula. I don't think it is comparing the correct numbers as for some Locations even though i can see the frequencies are not Equal it displays "Equal". Below is an example. Any help is appreciated!

A	B	C	D	E
AAA	6	C1 Higher	III	6​
BBB	9	#N/A​	JJJ	3​
CCC	6	C2 Higher	LLL	3​
DDD	3	C2 Higher	MMM	4​
EEE	10	Equal	HHH	6​
FFF	1	C2 Higher	LHR	1​
GGG	1	Equal	EEE	8​

I am using the formula you provided me with @irobbo314 (thank you!). =LOOKUP(B4/INDEX($E$4:$E$13,MATCH(A4,$D$4:$D$13,0)),{0,1,2},{"C2 Higher","Equal","C1 Higher"}) in column C.

 

[lrobbo314]

I thought I was clever with the lookup formula. This should do it.

can-you-find-the-pattern.xlsx
	A	B	C	D	E
1	A	B	C	D	E
2	AAA	6.00	Equal	III	6.00
3	BBB	9.00	#N/A	AAA	6.00
4	CCC	6.00	C2 Higher	CCC	8.00
5	DDD	3.00	#N/A	MMM	4.00
6	EEE	10.00	C1 Higher	HHH	6.00
7	FFF	1.00	#N/A	LHR	1.00
8	GGG	1.00	#N/A	EEE	8.00
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(INDEX($E$2:$E$8,MATCH(A2,$D$2:$D$8,0))=B2,"Equal",IF(INDEX($E$2:$E$8,MATCH(A2,$D$2:$D$8,0))<B2,"C1 Higher","C2 Higher"))

 

[yessir]

Thank you for your help! This worked perfectly!