Simple IF and/or question

T

Tedjars

New Member
Joined
Jan 4, 2019
Messages
3
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>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Emp[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]80 - 84.9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]85 - 89.9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]90 - 94.9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]95 - 100[/TD]
[/TR]
[TR]
[TD]4.9 +[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]1,200.00[/TD]
[TD="align: center"]2,000.00[/TD]
[TD="align: center"]3,000.00[/TD]
[/TR]
[TR]
[TD]4.8 - 4.89[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]1,200.00[/TD]
[TD="align: center"]1,800.00[/TD]
[TD="align: center"]2,800.00[/TD]
[/TR]
[TR]
[TD]4.7 - 4.79[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1,000.00[/TD]
[TD="align: center"]1,600.00[/TD]
[TD="align: center"]2,600.00[/TD]
[/TR]
[TR]
[TD]4.69 -[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1,000.00[/TD]
[TD="align: center"]1,500.00[/TD]
[TD="align: center"]2,500.00[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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
ABCDEFGH
1Emp80859095Rating4.7
20.005001,000.001,500.002,500.00Score80.00
34.705001,000.001,600.002,600.00Amount500
44.807001,200.001,800.002,800.00
54.907001,200.002,000.003,000.00
Sheet1
Cell Formulas
RangeFormula
H3=INDEX($B$2:$E$5,MATCH(H1,A2:A5,1),MATCH(H2,B1:E1,1))
 
Upvote 0
[TABLE="width: 1088"]
<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Emp[/TD]
[TD="width: 64"]80 - 84.9[/TD]
[TD="width: 64"]85 - 89.9[/TD]
[TD="width: 64"]90 - 94.9[/TD]
[TD="width: 64"]95 - 100[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]4.9 +[/TD]
[TD="align: right"]700[/TD]
[TD="class: xl22, align: right"]1,200.00[/TD]
[TD="class: xl22, align: right"]2,000.00[/TD]
[TD="class: xl22, align: right"]3,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4.8 - 4.89[/TD]
[TD="align: right"]700[/TD]
[TD="class: xl22, align: right"]1,200.00[/TD]
[TD="class: xl22, align: right"]1,800.00[/TD]
[TD="class: xl22, align: right"]2,800.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4.7 - 4.79[/TD]
[TD="align: right"]500[/TD]
[TD="class: xl22, align: right"]1,000.00[/TD]
[TD="class: xl22, align: right"]1,600.00[/TD]
[TD="class: xl22, align: right"]2,600.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4.69 -[/TD]
[TD="align: right"]500[/TD]
[TD="class: xl22, align: right"]1,000.00[/TD]
[TD="class: xl22, align: right"]1,500.00[/TD]
[TD="class: xl22, align: right"]2,500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col J[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Emp[/TD]
[TD]80 - 84.9[/TD]
[TD]85 - 89.9[/TD]
[TD]90 - 94.9[/TD]
[TD]95 - 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]row 15[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[TD="class: xl22, align: right"]1,000.00[/TD]
[TD="class: xl22, align: right"]1,500.00[/TD]
[TD="class: xl22, align: right"]2,500.00[/TD]
[TD="class: xl22, align: right"]1.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]mytable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.69[/TD]
[TD="align: right"]500[/TD]
[TD="class: xl22, align: right"]1,000.00[/TD]
[TD="class: xl22, align: right"]1,500.00[/TD]
[TD="class: xl22, align: right"]2,500.00[/TD]
[TD="class: xl22, align: right"]2.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.79[/TD]
[TD="align: right"]500[/TD]
[TD="class: xl22, align: right"]1,000.00[/TD]
[TD="class: xl22, align: right"]1,600.00[/TD]
[TD="class: xl22, align: right"]2,600.00[/TD]
[TD="class: xl22, align: right"]3.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.89[/TD]
[TD="align: right"]700[/TD]
[TD="class: xl22, align: right"]1,200.00[/TD]
[TD="class: xl22, align: right"]1,800.00[/TD]
[TD="class: xl22, align: right"]2,800.00[/TD]
[TD="class: xl22, align: right"]4.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]row 19[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]700[/TD]
[TD="class: xl22, align: right"]1,200.00[/TD]
[TD="class: xl22, align: right"]2,000.00[/TD]
[TD="class: xl22, align: right"]3,000.00[/TD]
[TD="class: xl22, align: right"]5.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]score =[/TD]
[TD="align: right"]83.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rating =[/TD]
[TD="align: right"]4.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]=OFFSET($E$14,VLOOKUP($F$26,mytable2,6),VLOOKUP($F$25,mytable,2))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]note that mytable2 is E15 to J19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]ie it does not include the top headings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank you for the suggestions! Ill try them out (excited :) )

The assistance greatly appreciated!

Cheers
Ted
 
Upvote 0
FormR said:
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
ABCDEFGH
EmpRating
Score
Amount

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4.7[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1,000.00[/TD]
[TD="align: right"]1,500.00[/TD]
[TD="align: right"]2,500.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"]80.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4.70[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1,000.00[/TD]
[TD="align: right"]1,600.00[/TD]
[TD="align: right"]2,600.00[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]500[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4.80[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]1,200.00[/TD]
[TD="align: right"]1,800.00[/TD]
[TD="align: right"]2,800.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4.90[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]1,200.00[/TD]
[TD="align: right"]2,000.00[/TD]
[TD="align: right"]3,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=INDEX($B$2:$E$5,MATCH(H1,A2:A5,1),MATCH(H2,B1:E1,1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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):

[TABLE="width: 539"]
<tbody>[TR]
[TD]0[/TD]
[TD]80[/TD]
[TD]85[/TD]
[TD]90[/TD]
[TD]95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] 500.00[/TD]
[TD] 1,000.00[/TD]
[TD] 1,500.00[/TD]
[TD] 2,500.00[/TD]
[TD][/TD]
[TD]Tenure[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4.7[/TD]
[TD] 500.00[/TD]
[TD] 1,000.00[/TD]
[TD] 1,600.00[/TD]
[TD] 2,600.00[/TD]
[TD][/TD]
[TD]Rating[/TD]
[TD]4.9[/TD]
[/TR]
[TR]
[TD]4.8[/TD]
[TD] 700.00[/TD]
[TD] 1,200.00[/TD]
[TD] 1,800.00[/TD]
[TD] 2,800.00[/TD]
[TD][/TD]
[TD]Score[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]4.9[/TD]
[TD] 700.00[/TD]
[TD] 1,200.00[/TD]
[TD] 2,000.00[/TD]
[TD] 3,000.00[/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD] 3,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]80[/TD]
[TD]85[/TD]
[TD]90[/TD]
[TD]95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] 500.00[/TD]
[TD] 1,000.00[/TD]
[TD] 1,500.00[/TD]
[TD] 2,500.00[/TD]
[TD][/TD]
[TD]Tenure[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4.7[/TD]
[TD] 500.00[/TD]
[TD] 1,000.00[/TD]
[TD] 1,800.00[/TD]
[TD] 2,800.00[/TD]
[TD][/TD]
[TD]Rating[/TD]
[TD]4.75[/TD]
[/TR]
[TR]
[TD]4.8[/TD]
[TD] 750.00[/TD]
[TD] 1,250.00[/TD]
[TD] 2,150.00[/TD]
[TD] 3,150.00[/TD]
[TD][/TD]
[TD]Score[/TD]
[TD]92[/TD]
[/TR]
[TR]
[TD]4.9[/TD]
[TD] 1,000.00[/TD]
[TD] 1,500.00[/TD]
[TD] 2,500.00[/TD]
[TD] 3,500.00[/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD] 1,800.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]80[/TD]
[TD]85[/TD]
[TD]90[/TD]
[TD]95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] 500.00[/TD]
[TD] 1,000.00[/TD]
[TD] 1,500.00[/TD]
[TD] 2,500.00[/TD]
[TD][/TD]
[TD]Tenure[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4.7[/TD]
[TD] 500.00[/TD]
[TD] 1,000.00[/TD]
[TD] 2,000.00[/TD]
[TD] 3,000.00[/TD]
[TD][/TD]
[TD]Rating[/TD]
[TD]4.9[/TD]
[/TR]
[TR]
[TD]4.8[/TD]
[TD] 1,000.00[/TD]
[TD] 1,500.00[/TD]
[TD] 2,500.00[/TD]
[TD] 3,500.00[/TD]
[TD][/TD]
[TD]Score[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]4.9[/TD]
[TD] 1,300.00[/TD]
[TD] 2,000.00[/TD]
[TD] 3,000.00[/TD]
[TD] 4,000.00[/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD] 1,300.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]80[/TD]
[TD]85[/TD]
[TD]90[/TD]
[TD]95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] 500.00[/TD]
[TD] 1,000.00[/TD]
[TD] 1,500.00[/TD]
[TD] 2,500.00[/TD]
[TD][/TD]
[TD]Tenure[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4.7[/TD]
[TD] 500.00[/TD]
[TD] 1,000.00[/TD]
[TD] 2,250.00[/TD]
[TD] 3,250.00[/TD]
[TD][/TD]
[TD]Rating[/TD]
[TD]4.84[/TD]
[/TR]
[TR]
[TD]4.8[/TD]
[TD] 1,250.00[/TD]
[TD] 1,750.00[/TD]
[TD] 3,000.00[/TD]
[TD] 4,000.00[/TD]
[TD][/TD]
[TD]Score[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]4.9[/TD]
[TD] 1,500.00[/TD]
[TD] 2,500.00[/TD]
[TD] 4,000.00[/TD]
[TD] 5,000.00[/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD] 1,750.00[/TD]
[/TR]
</tbody>[/TABLE]


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)
 
Upvote 0
Tedjars said:
There are 4 tables
Click to expand...

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
ABCDEFGHI
1Score
2TenureRating80859095Tenure:1.00
3005001,000.001,500.002,500.00Rating:4.81
404.75001,000.001,600.002,600.00Score:85.00
504.87001,200.001,800.002,800.00Amount:1250
604.97001,200.002,000.003,000.00
7105001,000.001,500.002,500.00
814.75001,000.001,800.002,800.00
914.87501,250.002,150.003,150.00
1014.91,000.001,500.002,500.003,500.00
11205001,000.001,500.002,500.00
1224.75001,000.002,000.003,000.00
1324.81,000.001,500.002,500.003,500.00
1424.91,300.002,000.003,000.004,000.00
15305001,000.001,500.002,500.00
1634.75001,000.002,250.003,250.00
1734.81,250.001,750.003,000.004,000.00
1834.91,500.002,500.004,000.005,000.00
Sheet1
Cell Formulas
RangeFormula
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)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
darrenmccoy
  • Question Question
Move 2 Columns and Values into separate Columns for each value formula
Replies
3
Views
824
RasGhul
RasGhul
fedcco
  • Question Question
Formula to add amout if paid or "0" if not paid in a specific column.
2
Replies
13
Views
2K
Fluff
Fluff
C
  • Question Question
Conditional Sumproduct Formula Error
Replies
3
Views
912
Eric W
Eric W
A
  • Question Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top