Simple IF and/or question

[Tedjars]

Good day to all. I have a table with multiple conditions with specific amounts if conditions are met, but Im stymied on how to create the IF and/or formula since the conditions have min/max ranges. Hoping for some help Thanks a lot!

Sample table below:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Emp	80 - 84.9	85 - 89.9	90 - 94.9	95 - 100
4.9 +	700	1,200.00	2,000.00	3,000.00
4.8 - 4.89	700	1,200.00	1,800.00	2,800.00
4.7 - 4.79	500	1,000.00	1,600.00	2,600.00
4.69 -	500	1,000.00	1,500.00	2,500.00

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

So, if Emp gets a score between 80 to 84.9 and a rating between 4.7 to 4.79, the amount should return "500", so on and so forth. Im trying to create a formula using IF and/or or if anyone can help me with another formula Id appreciate it much.

Many thanks!

Ted

 

[FormR]

Hi, welcome to the forum! If you can re-arrange your lookup table like below (using ascending order is important) then you can use a simple INDEX(MATCH(),MATCH()) formula.

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Emp	80	85	90	95		Rating	4.7
2	0.00	500	1,000.00	1,500.00	2,500.00		Score	80.00
3	4.70	500	1,000.00	1,600.00	2,600.00		Amount	500
4	4.80	700	1,200.00	1,800.00	2,800.00
5	4.90	700	1,200.00	2,000.00	3,000.00
Sheet1

Cell Formulas
Range	Formula
H3	=INDEX($B$2:$E$5,MATCH(H1,A2:A5,1),MATCH(H2,B1:E1,1))

 

[oldbrewer]

Emp	80 - 84.9	85 - 89.9	90 - 94.9	95 - 100
4.9 +	700	1,200.00	2,000.00	3,000.00
4.8 - 4.89	700	1,200.00	1,800.00	2,800.00
4.7 - 4.79	500	1,000.00	1,600.00	2,600.00
4.69 -	500	1,000.00	1,500.00	2,500.00
				col E					col J
				Emp	80 - 84.9	85 - 89.9	90 - 94.9	95 - 100
		row 15		0	500	1,000.00	1,500.00	2,500.00	1.00			mytable
				4.69	500	1,000.00	1,500.00	2,500.00	2.00
				4.79	500	1,000.00	1,600.00	2,600.00	3.00
				4.89	700	1,200.00	1,800.00	2,800.00	4.00			80	1
		row 19		10	700	1,200.00	2,000.00	3,000.00	5.00			85	2
												90	3
												95	4
												100	4
				score =	83.7
				rating =	4.75
					500
					=OFFSET($E$14,VLOOKUP($F$26,mytable2,6),VLOOKUP($F$25,mytable,2))
					note that mytable2 is E15 to J19
					ie it does not include the top headings

<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Tedjars]

Thank you for the suggestions! Ill try them out (excited )

The assistance greatly appreciated!

Cheers
Ted

 

[Tedjars]

Hi, welcome to the forum! If you can re-arrange your lookup table like below (using ascending order is important) then you can use a simple INDEX(MATCH(),MATCH()) formula.

Excel 2013/2016

	A	B	C	D	E	F	G	H
1	Emp	80	85	90	95		Rating	4.7
2	0.00	500	1,000.00	1,500.00	2,500.00		Score	80.00
3	4.70	500	1,000.00	1,600.00	2,600.00		Amount	500
4	4.80	700	1,200.00	1,800.00	2,800.00
5	4.90	700	1,200.00	2,000.00	3,000.00

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H3 =INDEX($B$2:$E$5,MATCH(H1,A2:A5,1),MATCH(H2,B1:E1,1)) <tbody> </tbody>

<tbody>
</tbody>

This works great! Again, thank you so much for the help. Was able to try it now since I was away for the weekend. This definitely helps me much! \m/.

Might I add another difficulty:

There are 4 tables, and instead of "EMP", its 1, 2, 3, 4 (for tenure years). Each tenured year have different corresponding amounts to the same headers (ratings and score):

0	80	85	90	95
0	500.00	1,000.00	1,500.00	2,500.00		Tenure	1
4.7	500.00	1,000.00	1,600.00	2,600.00		Rating	4.9
4.8	700.00	1,200.00	1,800.00	2,800.00		Score	99
4.9	700.00	1,200.00	2,000.00	3,000.00		Amount	3,000.00
1	80	85	90	95
0	500.00	1,000.00	1,500.00	2,500.00		Tenure	1
4.7	500.00	1,000.00	1,800.00	2,800.00		Rating	4.75
4.8	750.00	1,250.00	2,150.00	3,150.00		Score	92
4.9	1,000.00	1,500.00	2,500.00	3,500.00		Amount	1,800.00
2	80	85	90	95
0	500.00	1,000.00	1,500.00	2,500.00		Tenure	1
4.7	500.00	1,000.00	2,000.00	3,000.00		Rating	4.9
4.8	1,000.00	1,500.00	2,500.00	3,500.00		Score	80
4.9	1,300.00	2,000.00	3,000.00	4,000.00		Amount	1,300.00
3	80	85	90	95
0	500.00	1,000.00	1,500.00	2,500.00		Tenure	1
4.7	500.00	1,000.00	2,250.00	3,250.00		Rating	4.84
4.8	1,250.00	1,750.00	3,000.00	4,000.00		Score	89
4.9	1,500.00	2,500.00	4,000.00	5,000.00		Amount	1,750.00

<tbody>
</tbody>

So if I have to query that reads from the 4 tables and return the amount depending on tenure, I tried IFS + INDEX MATCH(()) but it always comes up with an error or too many arguments

Tenure:
Rating:
Score:
Amount:

Thank you (bow)

 

[FormR]

There are 4 tables

Hi, if instead of 4 tables you can layout one lookup table like this, then here is an option you can try.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1			Score
2	Tenure	Rating	80	85	90	95		Tenure:	1.00
3	0	0	500	1,000.00	1,500.00	2,500.00		Rating:	4.81
4	0	4.7	500	1,000.00	1,600.00	2,600.00		Score:	85.00
5	0	4.8	700	1,200.00	1,800.00	2,800.00		Amount:	1250
6	0	4.9	700	1,200.00	2,000.00	3,000.00
7	1	0	500	1,000.00	1,500.00	2,500.00
8	1	4.7	500	1,000.00	1,800.00	2,800.00
9	1	4.8	750	1,250.00	2,150.00	3,150.00
10	1	4.9	1,000.00	1,500.00	2,500.00	3,500.00
11	2	0	500	1,000.00	1,500.00	2,500.00
12	2	4.7	500	1,000.00	2,000.00	3,000.00
13	2	4.8	1,000.00	1,500.00	2,500.00	3,500.00
14	2	4.9	1,300.00	2,000.00	3,000.00	4,000.00
15	3	0	500	1,000.00	1,500.00	2,500.00
16	3	4.7	500	1,000.00	2,250.00	3,250.00
17	3	4.8	1,250.00	1,750.00	3,000.00	4,000.00
18	3	4.9	1,500.00	2,500.00	4,000.00	5,000.00
Sheet1

Cell Formulas
Range	Formula
I5	=LOOKUP(2,1/(($A$3:$A$18=I2)*($B$3:$B$18<=I3)),INDEX($C$3:$F$18,0,MATCH(I4,$C$2:$F$2,1)))