VLOOKUP

[TexJen]

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

 

[T. Valko]

If I understand what you want try this:

=H2*VLOOKUP(A2,Sheet2!A$2:C$6,3,0)

 

[Eric]

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

 

[TexJen]

Greatness! Thank you.

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

 

[TexJen]

You're 2 for 2 with my wacky questions in 2 days.

THANK YOU!!! x2

 

[T. Valko]

There's no such thing as a wacky question.

Thanks for the feedback!