Hlookup question

[eildydar]

I have a orders spreadsheet that has a total order amount on it and one other sheet that has handling % amounts based on a range of $ amounts. looks something like below







<table border = '1'> <tbody><tr><td>A</td><td>B</td><td>C</td><td>D </td><td>E</td><td>F</td><td> G</td></tr> <tr><td>1</td><td>Handling </td></tr> <tr><td>2</td><td> Total amt</td><td> 0</td><td> 2500</td><td>5000</td><td> 7500</td><td>10000</td><td>12500+</td></tr> <tr><td>3 </td><td>Handling chg</td><td> 2%</td><td>3%</td><td>4%</td><td>5%</td><td>6%</td><td>7%</td></tr></tbody></table>


So I need to take the total from one sheet and do the Hlookup on this sheet. The problem is that the total amount is more than 12500 and I can't think of a way to incorporate that into the HLOOKUP. To be upfront this is a homework assignment but I did attempt the problem before coming here. The notes say to use a HLOOKUP and MAX to get the job done....any ideas or point me in the right direction?

 

[JoeMo]

Since the total amt row is in ascending order, use Hlookup with the last argument ([range_lookup]) set to true. That will return the value that corresponds to the largest amt that is less than or equal to the lookup amount.

 

[T. Valko]

I have a orders spreadsheet that has a total order amount on it and one other sheet that has handling % amounts based on a range of $ amounts. looks something like below







<TABLE border=1><TBODY><TR><TD>A</TD><TD>B</TD><TD>C</TD><TD>D </TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR><TD>1</TD><TD>Handling </TD></TR><TR><TD>2</TD><TD>Total amt</TD><TD>0</TD><TD>2500</TD><TD>5000</TD><TD>7500</TD><TD>10000</TD><TD>12500+</TD></TR><TR><TD>3 </TD><TD>Handling chg</TD><TD>2%</TD><TD>3%</TD><TD>4%</TD><TD>5%</TD><TD>6%</TD><TD>7%</TD></TR></TBODY></TABLE>


So I need to take the total from one sheet and do the Hlookup on this sheet. The problem is that the total amount is more than 12500 and I can't think of a way to incorporate that into the HLOOKUP. To be upfront this is a homework assignment but I did attempt the problem before coming here. The notes say to use a HLOOKUP and MAX to get the job done....any ideas or point me in the right direction?

Homework?

They teach this stuff in school now days?

Computers hadn't been invented yet when I went to school!

You'll want to get rid of that plus sign:

Book1
	A	B	C	D	E	F	G
2	Handling
3	Total amt	0	2500	5000	7500	10000	12500
4	Handling chg	2%	3%	4%	5%	6%	7%
Sheet2

Sheet1 A1 = some number.

Here's what your homework assignment is asking for:

=HLOOKUP(A1,Sheet2!B3:G4,2)

But here's a better way:

=LOOKUP(A1,Sheet2!B3:G4)

 

[Peter_SSs]

Edit: Oops, Biff must have posted while I was considering/composing

Welcome to the MrExcel board!

Would you be allowed to just change the cell that contains "12500+" to just "12500"?

 

[eildydar]

It is 12500 on the sheet I was just using that to illustrate that everything above 12500 needs to fall in that category as well. I didn't understand the 3rd parameter and I was trying to tell it to go to row 3 which obviously there isn't a row 3 in the range as soon as I turned it to row 2 it worked fine. Stupid question but thanks for helping me with this.

 

[snoopyhr]

Just another alternative

Excel Workbook
	A	B	C	D	E	F	G
10	And if not allowed to remove the + try this
11	Total amt	0	2500	5000	7500	10000	+12500
12	Handling chg	2%	3%	4%	5%	6%	7%
13
14
15	5000	0.04
16	13600	0.07
17	Adjust range as desire as well as origin of table
Sheet10

 

[Peter_SSs]

And if not allowed to remove the + try this

Except there was no +

It is 12500 on the sheet I was just using that to illustrate that everything above 12500 needs to fall in that category as well. I didn't understand the 3rd parameter and I was trying to tell it to go to row 3 which obviously there isn't a row 3 in the range as soon as I turned it to row 2 it worked fine. Stupid question but thanks for helping me with this.

 

[snoopyhr]

Peter,

No need to highlit in red or quote.

I posted this before the clarification, just wanted to show even the scenario does not allow to make any modification ther is still a way to solve the problem.