Vlookup and if statements nested against three tables

[FMGSCG]

First time posting...been working on this for a few days now, hoping someone can help with the below situation; need solution quickly for meeting on Wednesday...

I have three tables (TenLim, TwentyLim & Excess - each 250+ lines over 6+ columns wide - this can't be changed) that are factoring into one formula dependent on a single number answer between 10-20.

The formula is:
=IF(D7="10",(VLOOKUP(D11,TenLim,2,FALSE)),IF(LOOKUP(D7,{"11","12","13","14","15","16","17","18","19"},(VLOOKUP(D11,Excess,2,FALSE))),IF(D7="20",(VLOOKUP(D11,TwentyLim,2,FALSE)))))

I receive a 'FALSE' answer on the above formula. I believe it's from the first VLOOKUP situation, due to D7=11 (If D7=10, the formula works). I thought my first IF statement's false_value was the second IF statement, but it doesn't work. I placed the LOOKUP function in the second IF statement due to having more than seven responses; is this the problem?

Any info you can pass along would be greatly appreciated! THANKS!

[darkyam]

Without having looked at the spreadsheet, I have some notes:
You shouldn't use quotes around numbers. Also, I think it should be IF(LOOKUP(D7,{"11","12","13","14","15","16","17","18","19" }),(VLOOKUP(D11,Excess,2,FALSE)) (misplaced parentheses)

[FMGSCG]

I updated the formula per your above suggestion...it now works for inputs of 10-19, yet an input of 20 does not work. I need it to pull from the 'TwentyLim' table if 20 is inputed, yet it still pulls from the 'Excess' table (inputs of 11-19). Any thoughts?

[darkyam]

I had to format D7 to General instead of Text. Then this formula works:
=VLOOKUP(D11,IF(D7=10,TenLim,IF(D7=20,TwentyLim,Excess)),2,0)

[jabryantiii]

nvm...darkyam already answered

[FMGSCG]

AWESOME! It worked...I fixed the cell references (you had them backwards in your reply) but it works now. Thank you very much! I will mark this as 'SOLVED'.

=VLOOKUP(D11,IF(D7=10,TenLim,IF(D7=20,TwentyLim,Excess)),2,0)

One more quick question, why did you put the zero at the end? I thought it needs to be true/false for a VLOOKUP??

[darkyam]

Yeah, I had them backwards the first time, but beat you to the corrections if you look at my edit time.

Anyway, True and 1 are the same thing in this argument (and, with proper forcing of Boolean values to be numbers, in the rest of Excel), as are False and 0. I just use 0 because it's shorter than writing False.

[FMGSCG]

Another question, what if I want to take that formula you created above and if they place '12' in, the formula will bring that '12' down to '10' behind the scenes and compute it. How can I do that?

[darkyam]

=VLOOKUP(D11,IF(OR(D7={10,12}),TenLim,IF(D7=20,TwentyLim,Excess)),2,0)

[ratcat]

My 2cents worth

Please Login or Register  to view this content.

But that's great if its solved.

Cheers

RC

[FMGSCG]

Thank you very much for the help!