Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
I can't wait to get this concept down so I don't have to ask . . . but I'm
not quite there yet. :) Worksheet 1 Column A has Position abbreviations. P1, P4, etc. Column I is "Expense Overage". Column H has an expense (dollar value) # in it. I need column I to multiply the # in column H by a number it finds using VLOOKUP, in a table in Worksheet 2. Worksheet 2 is a small table: Column A = Position abbreviations (same as other worksheet) Column C = rates, in decimal format. So, I need it to multiply the value in Column H from worksheet 1 by the rate in worksheet 2, based on the Position it reads. Read value in H, lookup the position in Column A (wrksht 1), see rate from table in wrksht 2, multiply by that #, return value. Thank you much in advance for any help! - TexJen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
If I understand what you want try this:
=H2*VLOOKUP(A2,Sheet2!A$2:C$6,3,0) -- Biff Microsoft Excel MVP "TexJen" wrote in message ... I can't wait to get this concept down so I don't have to ask . . . but I'm not quite there yet. :) Worksheet 1 Column A has Position abbreviations. P1, P4, etc. Column I is "Expense Overage". Column H has an expense (dollar value) # in it. I need column I to multiply the # in column H by a number it finds using VLOOKUP, in a table in Worksheet 2. Worksheet 2 is a small table: Column A = Position abbreviations (same as other worksheet) Column C = rates, in decimal format. So, I need it to multiply the value in Column H from worksheet 1 by the rate in worksheet 2, based on the Position it reads. Read value in H, lookup the position in Column A (wrksht 1), see rate from table in wrksht 2, multiply by that #, return value. Thank you much in advance for any help! - TexJen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
One thing which drives me nuts about vlookup is that fixed value for the
position in the array for the lookup column... if you ever make any changes to the structure of the data you might start pulling data from the wrong column. What I do in order to have the lookup column be dynamically addressed is use the column function in there. =H2*VLOOKUP(A2,Sheet2!A$2:C$6,column(c2)-column(a2)+1,0) that makes it a little messy, but then you don't have to worry about your vlookup function getting messed up if you add another column into the the middle of your table "T. Valko" wrote: If I understand what you want try this: =H2*VLOOKUP(A2,Sheet2!A$2:C$6,3,0) -- Biff Microsoft Excel MVP "TexJen" wrote in message ... I can't wait to get this concept down so I don't have to ask . . . but I'm not quite there yet. :) Worksheet 1 Column A has Position abbreviations. P1, P4, etc. Column I is "Expense Overage". Column H has an expense (dollar value) # in it. I need column I to multiply the # in column H by a number it finds using VLOOKUP, in a table in Worksheet 2. Worksheet 2 is a small table: Column A = Position abbreviations (same as other worksheet) Column C = rates, in decimal format. So, I need it to multiply the value in Column H from worksheet 1 by the rate in worksheet 2, based on the Position it reads. Read value in H, lookup the position in Column A (wrksht 1), see rate from table in wrksht 2, multiply by that #, return value. Thank you much in advance for any help! - TexJen |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Greatness! Thank you.
"Eric" wrote: One thing which drives me nuts about vlookup is that fixed value for the position in the array for the lookup column... if you ever make any changes to the structure of the data you might start pulling data from the wrong column. What I do in order to have the lookup column be dynamically addressed is use the column function in there. =H2*VLOOKUP(A2,Sheet2!A$2:C$6,column(c2)-column(a2)+1,0) that makes it a little messy, but then you don't have to worry about your vlookup function getting messed up if you add another column into the the middle of your table "T. Valko" wrote: If I understand what you want try this: =H2*VLOOKUP(A2,Sheet2!A$2:C$6,3,0) -- Biff Microsoft Excel MVP "TexJen" wrote in message ... I can't wait to get this concept down so I don't have to ask . . . but I'm not quite there yet. :) Worksheet 1 Column A has Position abbreviations. P1, P4, etc. Column I is "Expense Overage". Column H has an expense (dollar value) # in it. I need column I to multiply the # in column H by a number it finds using VLOOKUP, in a table in Worksheet 2. Worksheet 2 is a small table: Column A = Position abbreviations (same as other worksheet) Column C = rates, in decimal format. So, I need it to multiply the value in Column H from worksheet 1 by the rate in worksheet 2, based on the Position it reads. Read value in H, lookup the position in Column A (wrksht 1), see rate from table in wrksht 2, multiply by that #, return value. Thank you much in advance for any help! - TexJen |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
You're 2 for 2 with my wacky questions in 2 days.
THANK YOU!!! x2 "T. Valko" wrote: If I understand what you want try this: =H2*VLOOKUP(A2,Sheet2!A$2:C$6,3,0) -- Biff Microsoft Excel MVP "TexJen" wrote in message ... I can't wait to get this concept down so I don't have to ask . . . but I'm not quite there yet. :) Worksheet 1 Column A has Position abbreviations. P1, P4, etc. Column I is "Expense Overage". Column H has an expense (dollar value) # in it. I need column I to multiply the # in column H by a number it finds using VLOOKUP, in a table in Worksheet 2. Worksheet 2 is a small table: Column A = Position abbreviations (same as other worksheet) Column C = rates, in decimal format. So, I need it to multiply the value in Column H from worksheet 1 by the rate in worksheet 2, based on the Position it reads. Read value in H, lookup the position in Column A (wrksht 1), see rate from table in wrksht 2, multiply by that #, return value. Thank you much in advance for any help! - TexJen |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
There's no such thing as a wacky question.
Thanks for the feedback! -- Biff Microsoft Excel MVP "TexJen" wrote in message ... You're 2 for 2 with my wacky questions in 2 days. THANK YOU!!! x2 "T. Valko" wrote: If I understand what you want try this: =H2*VLOOKUP(A2,Sheet2!A$2:C$6,3,0) -- Biff Microsoft Excel MVP "TexJen" wrote in message ... I can't wait to get this concept down so I don't have to ask . . . but I'm not quite there yet. :) Worksheet 1 Column A has Position abbreviations. P1, P4, etc. Column I is "Expense Overage". Column H has an expense (dollar value) # in it. I need column I to multiply the # in column H by a number it finds using VLOOKUP, in a table in Worksheet 2. Worksheet 2 is a small table: Column A = Position abbreviations (same as other worksheet) Column C = rates, in decimal format. So, I need it to multiply the value in Column H from worksheet 1 by the rate in worksheet 2, based on the Position it reads. Read value in H, lookup the position in Column A (wrksht 1), see rate from table in wrksht 2, multiply by that #, return value. Thank you much in advance for any help! - 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 |