Help with formula

[normpam]

PRODUCT Dose
Flouroracil 4100
Flouroracil 400

There is another list which I have sorted by product (alphabetical ascending) and dose (in descending order)

PRODUCT
Flouroracil 2500
Flouroracil 1000
Flouroracil 500

Problem is to find each product from the first list and bring over the closest 'higher' amount for each product. If the second list only had two dose quantities, a simple VLookup works fine. But in this case, if I use a VLookup on the third item from the first list, and say that if the dose qty is less than the VLookup result, it will return 2500, when I need it to return 500. Does this make any sense?

Thanks!

 

[Z51]

Unfortunately, it does not make sense. "...if I use a VLookup on the third item from the first list..." when the first list in your example (PRODUCT DOSE) only contains two items.

Try providing several examples (slightly longer lists) and the expected results - as well as WHY those are the expected results. If you can, download the XL2BB add-in so you can easily grab cell ranges along with their formulas and post it to the forum. (Link is in reply message header.)

 

[normpam]

Unfortunately, it does not make sense. "...if I use a VLookup on the third item from the first list..." when the first list in your example (PRODUCT DOSE) only contains two items.

Try providing several examples (slightly longer lists) and the expected results - as well as WHY those are the expected results. If you can, download the XL2BB add-in so you can easily grab cell ranges along with their formulas and post it to the forum. (Link is in reply message header.)

How do I upload a file?

 

[Z51]

I think you can upload images, but those usually aren't very helpful, especially if folks here need to manually create your layout/data in their own workbook to test solutions. Best option is to download and use the XL2BB add-in, which you can get via the link in the Reply box (right end of the icons, after XLS, VBA, M, SQL, etc...