VLOOKUP and IF STATEMENT Combos

Hi,

Having trouble putting together the correct formula. Any and all help will be greatly appreciated. My set up reads similar to this. A1 is a drop down box with different words. B1 is a drop down box with different amounts. C1 is a manual input cell where numbers will be entered. D1 is where I will be entering my formula. To the right is a table where values will have to be looked up and related back to cell D1. So the table looks like this: Top Culumn (merged Cells) A1 word X4, Second column heading underneath C1 ranges X 4 repeated under every A1 word. Under the 4 repeated C1 ranges is different amounts for the look up. The the left, the rows are the number value looked up from drop down B1. I hope that was clear. So basically, the user will be selecting a word from drop down box A1 and B2, they are not dependent on one or the other except for the table obviously--the drop downs are independent, and then the user will input a number into C1. Based on this cell D1 will pull a number from the aformentioned table.

Thank you guys so much!

Could you upload a sample workbook? It would make it easier to get it right at first try.

I would have to make some changes to your table in order to help you. I don't how you feel about that?

Would this do?

in D1, put the following formula.....

=VLOOKUP(B1,RangeAll,LEN(A1)*4-INT(C1/100)+1,FALSE)

I gave your data L12:AB15 a named range called rangeAll, but you could just use a reference if you want

Below are 2 formulas to place in H25; 1) is the simplest, and you can use that one if there are only the 2 choices of multiplier. 2) You can use if you at some point will be expanding the number of multiplier, making compound =IF()'s unfeasible.

1) =G25*IF($E$25=$E$50,$E$51,$F$51)
2) =G25*INDEX($E$51:$F$51,MATCH($E$25,$E$50:$F$50,0))

Does this answer your question?