Hlookup -next largest number

**samlim** · 01-10-2010, 04:10 AM

Hi,

I have a Hlookup function which is currently returning me the closest match that is lower.

However, I want it to return the higher value.

I have attached an explanation of what I'm trying to do.

Thanks in advance,
Sam

**pike** · 01-10-2010, 05:02 AM

hi samlim
can you change values from 0,10,20,30,40 to 9,19,29,39,49,1000

**pike** · 01-10-2010, 05:21 AM

wrong infomation

**pike** · 01-10-2010, 05:31 AM

the easy way would be to change the values in the quanity rows

**pike** · 01-10-2010, 07:18 AM

Hey samlim
use this it will at the correct amount for the item to pull into the next column for the Hlookup
=HLOOKUP($D5+SUMPRODUCT(--(C5={"A","B","C","D"})*({10,20,100,5})),OFFSET($J$3:$O$4,MATCH($C5,$I$5:$I$14),0),2,TRUE)

**oldchippy** · 01-10-2010, 07:31 AM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!