Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |