Compare two values in two rows and return the higher value for two categories.

In G2, copied down:

=IF(LEFT(E2,2)="T1","OK",IF(F2>F1,"Higher Rate","OK"))

In G2 copied down:

=IFNA(IF(VLOOKUP(INDEX('Tier Code'!$C$2:$C$14,MATCH(Sheet1!E2,'Tier Code'!$D$2:$D$14,0)),$E$2:$F$27,2,0)<F2,"Higher Rate","OK"),"OK")

Hi,

More explanation is needed. You mention Tier 1 and Tier 2 yet your tier codes are 4 numerics. Do we assume it's the second digit that determines the tierv 1 or 2?

Why is T235 marked as Higher when the highest Tier 1 code is 192? WHen considering 'higher' are all rows considered as a whole or only each pair of values? i.e. rows 2&3, 4&5 etc? In which case

Formula:

Please Login or Register  to view this content.

I don't understand your request. You only show one Branch Z8912.

Please ensure you include at least two branches in your example data and manually add the results you want to see. Clearly identify which cells are results and which are original data. Make sure the sample data is truly representative. i.e. are different branches always shown in a continuous run of cells or do branches appear between other branches?

This thread is marked as solved - please remove the tag for now.

can you explain why the answer at Post 2 is incorrect?

How is this different? Using the formula I gave you in post #3 in column H:

Excel 2016 (Windows) 32 bit

	D	E	F	G	H
1	Branch ID	Tier Code	Rate	Result
2	Z8912	T110	192	Ok	OK
3	Z8912	T220	96	Ok	OK
4	Z8912	T115	177	Ok	OK
5	Z8912	T225	77	Ok	OK
6	Z8912	T120	181	Ok	OK
7	Z8912	T230	55	Ok	OK
8	Z8912	T125	151	Ok	OK
9	Z8912	T235	155	Higher rate	Higher Rate
10	Z8912	T130	159	Ok	OK
11	Z8912	T240	70	Ok	OK
12	Z8912	T135	174	Ok	OK
13	Z8912	T245	97	Ok	OK
14	Z8912	T140	147	Ok	OK
15	Z8912	T250	96	Ok	OK
16	Z1092	T110	143	Ok	OK
17	Z1092	T220	99	Ok	OK
18	Z1092	T115	174	Ok	OK
19	Z1092	T225	187	Higher rate	Higher Rate
20	Z1092	T120	181	Ok	OK
21	Z1092	T230	110	Ok	OK
22	Z1092	T125	158	Ok	OK
23	Z1092	T235	164	Higher rate	Higher Rate

Sheet: Sheet1

Well, none of the solutions offered will work if you keep shifting the goalposts, will they? Do you expect us to be mind readers or to be able to predict the future? If so, I'm afraid you are going to be disappointed.

Good luck with this.

Try:

Please Login or Register  to view this content.