Searching within table

Hi Eveyone,

I have an interesting problem that I am hoping someone could help me with. I have attached an excel file with the table in question.

Cells (B2, C2, & D2) are user entered. I would like to be able to take that input and look up the corresponding criteria in table and be able to return the rebate amount.

Each category and corresponding rebate is seperated/distinguished by either wattage (upper and lower limit), Lumen (upper and lower limit), or nothing. If the user number falls in between the upper/lower limit of the category in question then it should return the rebate amount.

Ex. user entered
Category = Low-Bay Luminaires for Commercial and Industrial Buildings
wattage = 150
lumen = 2600

looking in the table, you will find that it is the lumen range that determine the rebate amount. the 2600 falls between 2500 (lower limit) and 4999 (upper limit), so the rebate that is select should be $10. I am looking for a formula that will accomplish this.

Im not sure if that explains it well.

I appreciate any help. thank you

yes, that is correct. No limits exist. it can have any wattage.

Hi misrasomendra,

That worked! thank you. There is another small piece maybe you could help me with. If you look at the table you will see occ sensor or ballast and there are no upper or lower limits. As long as you select ballast in the drop down, it should pull the rebate ( there is only one option). How would you incorporate that into your equation?

Thanks again! Appreciate your help

Hi newdoverman,

That worked as well. Thank you! This table is part of a much larger list. I will test both methods and see which would be easier to maintain in the future.

Thank again newdoverman. appreciate the help!

Thank you newdoverman. I am working on finishing up the list in the next day or two and i will post it here. Thanks again

Good morning,

I have attached the complete list. I appreciate any help.

Thank you

Hi newdoverman,

Ive attached the complete list. Thank you.

Hi,

See the file.

Thank you very much misrasomendra. That solve it for me. Appreciate all the help!

Thanks for the feedback.

You can mark the thread as SOLVED if your query is solved satisfactory and can give some rep points to those who you think really helped you by clicking the star of add reputation under our comments.

Hi Misrasomendra,

I have added the rep points.

Thanks for the rep point.

Regards,

Hi misrasomendra,

I have attached the final version of that sheet. I transfered the code that you wrote up but for some reason I am not get rebate results populating for any category that either uses the upper/lower wattage limit or the upper/lower lumen limit. It only populates for the general category.

If you look at column D on the clean sheet tab, you will find the rebates column. the rebates are not populating for anything that require wattage or lumen info. If you could take a quick look that would be awesome. Appreciate your help misrasomendra.

Thank you

I did not understood what you are saying?

Regards,

If you look at column D, you will see that the cells have the formula you came up with but some of the cells are blank (eg. D12, D15, D30). there is an error with them. Could you look at the formula and see if you could help me with that? Its the same forumula from the other sheet you uploaded. Let me know if it is still not clear.

Thanks