If Statement

[FanofExcel18]

Need Help

I'm weighting hours.

Example: Work hours for the month is 150

1 = 120<
2 = 121-135
3 = 135-150
4 = 150>

 

[Fluff]

I still don't understand, both formulae supplied give the answers you have shown.
There was no mention in your OP of a "base number" & you have not shown how that fits in.

 

[FanofExcel18]

[TABLE="width: 380"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Time[/TD]
[TD]Amount[/TD]
[TD]Hours[/TD]
[TD][/TD]
[TD]Weighting[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]142[/TD]
[TD] [/TD]
[TD]125 or Less[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]126 to 135[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]162[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]136 to 142[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]136[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]142 or greater[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Ok, I understand where the "base number" fits it (ie the max weighting), but the other other weightings have no changed. Are they also dependant on the base number?

 

[FanofExcel18]

Ok, I understand where the "base number" fits it (ie the max weighting), but the other other weightings have no changed. Are they also dependant on the base number?

The Base will be whatever that month hours are. Each of the weightings will change in range depending on the month. Those I can physically change. I don't know how to get the IF statement (if that is what should be used) to push the result correctly. I hope that answers your question.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
7	123	1	142		9999	4
8	127	2			142	3
9	162	4			135	2
10	136	3			125	1
Sheet2

Cell Formulas
Range	Formula
B7	=INDEX(E$7:F$10,MATCH(A7,E$7:E$10,-1),2)

where E7:F10 are a lookup table somewhere in the workbook

 

[FanofExcel18]

How about

Excel 2013/2016

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]7[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9999[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=INDEX(E$7:F$10,MATCH(A7,E$7:E$10,-1),2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



where E7:F10 are a lookup table somewhere in the workbook

So I think this will work. Here is my problem now, I'm getting #N/A.

I think it has something to do with the 2 at the end for the column. I'm not as familiar with INDEX MATCH.

My first lookup cell is I6, my table is located AA5 to AB8

 

[Fluff]

Assuming the table is on the same sheet & it's sorted descending, try
=INDEX(AA$5:AB$8,MATCH(I6,AA$5:AB$8,-1),2)

 

[FanofExcel18]

Assuming the table is on the same sheet & it's sorted descending, try
=INDEX(AA$5:AB$8,MATCH(I6,AA$5:AB$8,-1),2)

It's not working, one last thing, if this matters. the Formula is in cell S6

 

[Fluff]

The location of the formula is irrelevant.
Is all the data on the same sheet?
Also is you lookup table sorted descending?

 

[FanofExcel18]

The location of the formula is irrelevant.
Is all the data on the same sheet?
Also is you lookup table sorted descending?

I figured it out!!! And it was my error totally. I duplicated the row/column formula: =INDEX(AA$5:AB$8,MATCH(I6,AA$5:AB$8,-1),2)
When it should have been: =INDEX(AA$5:AB$8,MATCH(I6,AA$5:AA$8,-1),2)

It works, thank you for your help and patience!