Excel 2007 : Conditional Formatting / Lookup

Hi,

you can use VLookup for that

=VLOOKUP(A1,$M$1:$N$10,2,False)

Where

A1 is the value you have entered and want to find the rate for.
M1:N10 is a two column range with the service types listed in column M and the rates in column N
2 means to return the value from the second column of the lookup table in the row where the value was found
FALSE means that only exact matches are returned.

Adjust the cell references to your spreadsheet layout.

hth

Look here:

Okay - both posts helped (repped both). I used VLookup because I already had a table created that way for the rates.

Last question, I want all the other cells in the column to do the lookup the same way but when I auto fill the formula down the LOOKUP changes where it is looking for cells I do not want it to and spits back #N/A.

I have attached the file for you to see what I mean.

Hi,

before you copy your formula down you'll have to apply absolute references, otherwise they'll be adjusted as the formula is copied down the rows.

=HLOOKUP(A1,$K$1:$M$2,2,0)

will not change the K1:M2 reference.

hth

try

=if(isblank(A1),"",HLOOKUP(A1,$K$1:$M$2,2,0))