Assigning value based upon number within a range. excel 2003

[elewis1220]

Greetings all,

Glad to be able to seek the knowledge and wisdom of all the people who utilize this forum, so here we go.

I have attached an small excel document with 2 sheets that provide examples of what I'm trying to achieve.


Sheet 1:

Given a number, I need a value to auto populate within a specified cell (positive or negative)


Sheet 2:
Given a number, I need a value to calculated and then auto populate within a specified cell .

As you can see in the examples, there are multiple ranges that need to be in the criterias. I am unsure if there is some kind of compound IF statement, lookup or, something. Or, if this needs to be done in the Visual Basic Editor please advise, Thanks

[popipipo]

=VLOOKUP(ABS(B10),$A$2:$C$8,3,1)

Try this one

[elewis1220]

Thank you, is this lookup in regards to sheet 1 or sheet 2?

[popipipo]

If you had tried it you knew it:
sheet1: =VLOOKUP(ABS(B10),$A$2:$C$8,3,1)
sheet2: =ABS(VLOOKUP(B15,$A$2:$B$6,2,1))

[elewis1220]

popipipo,

My apologies I jumped the gun when I asked that question I was in the middle of plugging in the formula. It worked as it should and I thank you for that. Now that I was able to plug in the formula I had one question.

Sheet 1:

Any value in the (.10) decimal place, positive or negative gives me a #NA message in the cell. I assume because of the ABS part within in the formula.

- Is there anyway I can tweak the formula to take decimal values under 1?

[popipipo]

What value you expect with a input of <1?

[elewis1220]

Now that I think about there wouldn't be an instance in which a value would be <1. I was just over analyzing scenarios on my part.

Thank you so much for your assistance. any pointers or literature you reccommend for increasing my excel knowledge with formulas & functions?

[popipipo]

Follow this forum and read a lot of these questions with there answers.