IF & VLOOKUP Together

[TexJen]

Thanks in advance for any assistance!

Worksheet 1 has:
Column A = Positions
There are 6 levels, ie: P1, P4, T9,...

Column G = Expenses, in $.

Worksheet 2 has:
A table listing the positions (same as Column A in 1st worksheet).
Also contains 'Max Spending Level' in $, in Column B.

How can I use IF & VLOOKUP nested together to:
Have it look at the Position in Column A in worksheet 1, then look at the
Expenses in Column G (worksheet 1) and see if those expenses are over the
'Max Spending Level' on the Table in the 2nd worksheet (column B), depending
on the Position in Column A in that same table?

Or, otherwise stated:
I need Worksheet 1 to reference Worksheet 2's table.
I need it to return a value of 0 if it is not over the Max Spending Level,
but if it is over, then I need it to return that value.

If I've been too verbose and not clear, please let me know. I will try
anything.
Thanks much!
- TexJen

 

[Fred Smith]

I expect you want something like:

=if(vlookup(...)>vlookup(...),0,vlookup(...))

Does this help?

Regards,
Fred.

 

[T. Valko]

Try this:

=IF(VLOOKUP(A2,Sheet2!A$2:B$7,2,0)>G2,0,VLOOKUP(A2,Sheet2!A$2:B$7,2,0))

 

[TexJen]

Awesome! Except I left out a piece of information. oops

It is returning the 0's correctly.
But what I failed to relay, was . . . for the ones where G2 is greater than
the table value from Worksheet 2, rather than it returning the value it finds
on the Table, I need it to give me the overage.

Or, if G2 is 650 and it sees from table in Worksheet 2, that the max was
500, then I need it to return 150.

I didn't relay that yesterday. You gave me exactly what I asked for and
it's just a tiny bit from where I need to be. I tried doing an extension of
what you supplied, after the 2, in the last part . . .G2-, but I couldn't
figure out how to ask it to reference the table value to subtract it.

Thanks so much again - -it's almost done.
I've only just started using these message boards and find the concept
AWESOME. Thank goodness for folks like you guys!!!!

-texjen

 

[TexJen]

Nevermind the e-mail I just sent you about needing the 'difference'.
Not sure what I did the first time but I just went in and put "G2-" before
the last VLOOKUP part and it's PERFECT.
It returns the overage value.

THANK YOU AGAIN for your assistance. It was brilliant!

 

[T. Valko]

You're welcome. Thanks for the feedback!