|
IF & VLOOKUP Together
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 |
IF & VLOOKUP Together
I expect you want something like:
=if(vlookup(...)vlookup(...),0,vlookup(...)) Does this help? Regards, Fred. "TexJen" wrote in message ... 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 |
IF & VLOOKUP Together
Try this:
=IF(VLOOKUP(A2,Sheet2!A$2:B$7,2,0)G2,0,VLOOKUP(A2 ,Sheet2!A$2:B$7,2,0)) -- Biff Microsoft Excel MVP "TexJen" wrote in message ... 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 |
IF & VLOOKUP Together
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 "T. Valko" wrote: Try this: =IF(VLOOKUP(A2,Sheet2!A$2:B$7,2,0)G2,0,VLOOKUP(A2 ,Sheet2!A$2:B$7,2,0)) -- Biff Microsoft Excel MVP "TexJen" wrote in message ... 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 |
IF & VLOOKUP Together
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" wrote: Try this: =IF(VLOOKUP(A2,Sheet2!A$2:B$7,2,0)G2,0,VLOOKUP(A2 ,Sheet2!A$2:B$7,2,0)) -- Biff Microsoft Excel MVP "TexJen" wrote in message ... 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 |
IF & VLOOKUP Together
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "TexJen" wrote in message ... 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" wrote: Try this: =IF(VLOOKUP(A2,Sheet2!A$2:B$7,2,0)G2,0,VLOOKUP(A2 ,Sheet2!A$2:B$7,2,0)) -- Biff Microsoft Excel MVP "TexJen" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com