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

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

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

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

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

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