INDEX MATCH Formula? - Match value and determine if number in range return a value

[swiffer01]

Trying to write a formula for the data sample below. TBL 1 and TBL 2 actual data sets are much larger and contain additional values.

If TBL 1 REP TYPE "AE1-C" = TBl 2 REP TYPE "AE1-C and TBL 1 TOTAL ACCTS
is between TBL 2 Total Accts Low and Total Accts High, return Points
TBL 1			TBL 2
REP TYPE	TOTAL ACCTS		REP TYPE	Total Accts Low	Total Accts High	Points
AE1-C	1041		AE1-C	0	374	0
AE1-C	178		AE1-C	375	399	1
AE1-C	1159		AE1-C	400	424	2
AE1-C	1028		AE1-C	425	449	3
AE1-C	1660		AE1-C	450	500	4
AE2-C	77		AE1-C	501	5000	0
AE1-C	840		AE2-C	0	374	0
AE1-C	719		AE2-C	375	399	1
MAE1-C	180		AE2-C	400	424	2
AE1-C	742		AE2-C	425	449	3
AE1-C	754		AE2-C	450	500	4
AE1-C	1330		AE2-C	501	5000	0
MAE1-C	412		AE3-C	0	374	0
AE1-C	1440		AE3-C	375	399	1
MAE1-C	1333		AE3-C	400	424	2
AE1-C	463		AE3-C	425	449	3
AE2-C	1074		AE3-C	450	500	4
AE1-C	938		AE3-C	501	5000	0
AE1-C	979		MAE1-C	0	104	0
MAE1-C	77		MAE1-C	105	114	1
			MAE1-C	115	124	2
			MAE1-C	125	134	3
			MAE1-C	135	150	4
			MAE1-C	151	5000	0

<colgroup><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[jamcall]

Where do you want points returned? Next to each line in TBL 1?

The following will do that:
=INDEX($D$3:$G$26,MATCH($A3,IF(($B3 >= $E$3:$E$26)*($B3 <= $F$3:$F$26),$D$3:$D$26),0),4)

TBL 1 data starts in A3, TBL 2 data starts in D3, the above formula is in C3 (entered with ctrl-shift-enter) and dragged down.

HTH,
~ Jim

 

[swiffer01]

jamcall - thx for the assist!!