HLookup? Nested IF Statements?

[ladyck3]

I will be completely honest, I am at a loss here. This is what was asked of me but quite frankly, it threw me into a loop.

This is the question, verbatim... can you gurus figure this out without the spreadsheet being in front of you?

Once again, I appreciate you all very much, seriously!

And I quote
============================================
I would like to be able to test for a range, so if "a1" is less than 12.5 but greater than 7.5, it returns "b".

(Thus far they have) used nested if loops, but since you can have only 7 ifs in a formula, it gets tedious. A look up box with ranges (if <=, etc) would be great.

Here is an example of the nested loops: =IF(C336<6.25,5,IF(C336<8.75,7.5,IF(C336<11.25,10,IF(C336<13.75,12.5,IF(C336<16.25,15,IF(C336<18.75,17.5,20))))))

They would like to have maybe 25 tests in the loop.

The test data provided is as follows, (with no cell references or anything so if this is not possible to figure without more information, let me know and we'll just close the thread)

10 b 15 10 5
c b a

I'm like... huh? I think we need more data but I'm not even sure what to ask... and BTW, this is like 3rd hand.

Sorry to be so detailed, yet so vague.





LadyCK3

 

[Zathras]

This would seem to be simpler than your situation in thread68-639572. You should be able to use one of the lookup functions to accomplish what you need.

 

[ladyck3]

Ok, thanks Zathras...

See, This is so far above my understanding that I was not even aware I'd asked this before.

I'm NOT an Excel or Access export by any means... I thank you for pointing me in the right direction.

I think I need more details too...

We can close this thread as heaven knows, enough people spent enough time on the last one.

THANKS!!!


LadyCK3

 

[Bowers74]

I am assuming that the first 10 in the example data is the number being checked and the first &quot;b&quot; is the value being returned using an HLOOKUP() on the &quot;table&quot; to the left.

The bottom line of the &quot;table&quot; are the cells containing the nested If's based on the top line of the &quot;table&quot;.

If I understood the &quot;question&quot; correctly, the user would like to have all of this in one cell without having to have to use the &quot;table&quot;.

Is that right so far?


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ladyck3]

Mike,

I honestly do not have an answer. Please don't trouble yourself over this one, I thought I'd see what I could do before the reinforcements showed up. They did...I've passed it on to someone more knowledgeable....

THANKS!



LadyCK3

 

[jdhilljr]

I think if you set a vlookup with your data listed as such
A B
1 A
2 A
3 A
4 A
5 A
6 B

C336 C337
4 VLOOKUP(C335,A1000:B1000,2,0)
I'M AT HOME AND DON'T HAVE EXCEL HERE SO CHECK THE SYNTAX FOR VLOOKUP. IF THIS IS FOR A GRADE DEPENDING ON HOW MANY WRONG ANSWERS YOU CAN CONTINUE IN COLUMN C WITH A DIFFERENT GRADE SCALE FOR OTHER TESTS AND CHANGE THE 2 IN VLOOKUP TO WHICHEVER COLUMN YOU WANT

 

[jdhilljr]

Sorry in the vlookup it should be C336 not C335

 

[ladyck3]

PLEASE do NOT stress over this one.

More information was gathered and it involves a lot more than just this and its something that was forwarded to another entity for assistance.

I do appreciate your time and assistance EVERYONE, but

CONSIDER THIS THREAD CLOSED
Please



LadyCK3