Condiitional Formula Help needed

Please see the attached excel for the problem statement....and sample data

Thanks for your help and time.

Hi there, and welcome to the forum.

An approach using VLOOKUP is attached.

Should I be using NESTED IF statement? I need to find the range first and value next.....Can it be done with NEsted IFs?

THanks for your time.

I wouldn't. In order to do this with nested IF statements, you're looking at a monster formula that would be a real pain to maintain. With the setup I gave you, it will run more efficiently (probably not a big issue here), but more imporant is that you could easily insert a new percentage in the middle of the table if you needed to. (Just watch carefully as those %'s actually have decimals attached in order to match your >= rules.)

If you can clarify a bit more as to what you're trying to find, I can help you with it...

Thanks...Is there a limitation on how many VLOOKUPS we can add to this formula?
For example, if I want to add HIGH % values, will that matter?

=B4*IF(B2="Low",VLOOKUP(B3,D6:E9,2,TRUE),(IF(B2="Med",VLOOKUP(B3,G6:H9,2,TRUE),VLOOKUP(B3,J6:K9,2,TRUE))))

Will this be right?

Can you explain the decimals concept for the >= rules?

if I have the range is like this...what would be hte decimal values for this?

Sure, so in the set of data you just provided, you actually don't need to add decimals to the values. The difference is that in your earlier example you were asking for numbers >x%. Here you're asking for >=x%.

So the table we'd build is as follows:
VLOOKUP works like this: =VLOOKUP(Value to look up, Range to look in, Column to return, Exact or approximate match)
For example: =VLOOKUP(69.9,A1:B4,2,TRUE)

When you're using an approximate match in a VLOOKUP statement, the numbers in the first column must be in ascending order. The function then returns the corresponding column for the highest row that does not exceed the number you gave.

So in this case, if we looked for a value of 69.9, VLOOKUP would look at the first row and perform the test "Is 69.9>=0", which it is. Then it would check "Is 69.9>=70", which is obviously not true. It would therefore return 1 (if we told it to look in the second column)

If we looked up 70, we'd get "Is 70>=0", which it is, then "Is 70>=70", which it is and "Is 70>=80", which it's not. The last true result is 70, and it would therefore return us the 2 from that row.

Does that make sense so far?

So the issue we had in the previous scenario was that you were looking for >2%, where VLOOKUP want >=2%. So what I did to fix that was added an insignificant digit to each number to force it to not be equal when you put in a round percentage.

As far as having more VLOOKUPS, sure you can do that. Ultimately though it might be better to build one large table and use a single VLOOKUP with a MATCh formula to pull out the column. That may sound a bit complicated, but we can help with it if you'd like to go that route.