Excel help please - Which function do I use?

Forums › Microsoft Windows › Excel help please - Which function do I use?

astrobox

23 posts

Geek

# 39840 20-Aug-2009 11:15

I need to figure out how to make the "rate" column show the right rate when I type the contractor type into the "contractor" column based on the contractors and rates...

(See pic) https://cdn.geekzone.co.nz/imagessubs/blog1515ff08438b877638b04b190bdd34a0.jpg

Do I use an IF function or a LOOKUP function, can anyone tell me the formula?

This is driving me (more) mental!

PS: I'm a noob here so I'm really sorry if I posted this in the wrong place :(

1 | 2

34 posts

Geek

# 248972 20-Aug-2009 11:25

Try

=IF(B12="","",VLOOKUP(B12,A5:B8,2,FALSE))

I put the If statement in so n/a is not shown when empty otherwise the lookup is fine

astrobox

23 posts

Geek

# 248974 20-Aug-2009 11:26

Oh you legend! I'll give it a go, thanks so much!! :D

Jimmyoo0

19 posts

Geek

# 248976 20-Aug-2009 11:33

or try

=IF(B12=A5,B5,IF(B12=A6,B6,IF(B12=A7,B7,IF(B12=A8,B8," "))))

This will leave the cell blank if there is no contractor

astrobox

23 posts

Geek

# 248982 20-Aug-2009 11:41

Jimmy that won't work because I can't use A5 to A8 as the references, I have to use the word "Electrician" (A5 is "Electrician:" with a colon), but I'll try replacing your A5 refs as words and see how I go. That looks right :)

nzjwnz - yours didn't work but thanks so much for trying :)

nzjwnz

34 posts

Geek

# 248983 20-Aug-2009 11:44

Jimmyoo0: or try

=IF(B12=A5,B5,IF(B12=A6,B6,IF(B12=A7,B7,IF(B12=A8,B8," "))))

This will leave the cell blank if there is no contractor

Yes that would work too but would get a bit longwinded and hard to maintain if more contractor options are added. In the formula i used above it deals with blanks first but you will still get N/A if the contractor value does not match exactly can get around this either by changing 'False' to 'True' and using excel to match or add an IfError e.g.
=IF(ISERROR(VLOOKUP(B12,A5:B8,2,FALSE)),"",VLOOKUP(B12,A5:B8,2,FALSE))

andrew027

1256 posts

Uber Geek

# 248985 20-Aug-2009 11:45

Given your data as supplied in the image, I would do the following:
1. Remove the colons from the end of the contractor types in column A (otherwise you're going to have to type them in the Contractor column all the time).
2. Make sure your contractor types are sorted in alphabetical order (as they are in the image).
3. In the selected cell (C12 in the image) enter the following formula, making sure you have the correct start and end rows in the formula (rows 5 and 8 respectively in the image):
=LOOKUP(B12,A$5:A$8,B$5:B$8)
4. Copy this formula to all the cells in the Rate column.
5. Format the cells in Rate to the currency style you're using.

Then, if you entered Painter in B12, C12 would show $25.00.

The "problem" with the formlua in Step 3 is that if the Contractor column is empty, you will get a #N/A error in the Rate column. Personally I hate that, so would do something like this, just to tidy it up:
=IF(ISBLANK(B12),0,LOOKUP(B12,A$5:A$8,B$5:B$8))
So if the Contractor column is blank, the Rate column will show $0.00 (or $ - depending on your format selection).

&rew

astrobox

23 posts

Geek

# 248986 20-Aug-2009 11:45

Yus!! It worked :)

=IF(B13="Builder",$B$5,IF(B13="Electrician",$B$6,IF(B13="Painter",$B$7,IF(B13="Plumber",$B$8," "))))

Thanks guys :)

nzjwnz

34 posts

Geek

# 248990 20-Aug-2009 11:55

Glad you got it to work, i didn't see the colon at the end, changing false to true in my original formula would probably work or you could add it into the lookup value e.g.

=IF(B12="","",VLOOKUP((B12&":"),A5:B8,2,FALSE))

Jimmyoo0

19 posts

Geek

# 248995 20-Aug-2009 11:58

To make it easier to select what contractor you need you could always have a drop down list for cell B12
do this by selecting cell B12 and go to Data>Validation chose list and A5:A8

astrobox

23 posts

Geek

# 248996 20-Aug-2009 12:03

Yes Jimmy but I can't use A5:A8 because I need it to say "electrician" and not "electrican:" (with colons)...

It's for an exercise at my course that the tutor laid out for me and the tutor is away today, and the workbook aint helping. lol.

andrew027

1256 posts

Uber Geek

# 248997 20-Aug-2009 12:11

I would go with LOOKUP rather than the nested IF statements. It's simpler, for one thing, but if you think you'll have to add more contractor types in the future (carpet layer, gas fitter, tiler, etc.), then:
- as nzjwnz said, it will get messy maintaining the formula, and
- I think you can only have seven levels of nesting in Excel, which limit the number of types you can have using the IFs

astrobox

23 posts

Geek

# 248998 20-Aug-2009 12:13

Thanks Andrew. I'm going to lunch. F*ck Excel. Haha

rvangelder

352 posts

Ultimate Geek

# 249555 21-Aug-2009 21:59

Watch out for absolute versus relative references.
=IF(B12="","",VLOOKUP((B12&":"),A5:B8,2,FALSE))

should be
=IF(B12="", "", VLOOKUP((B12 & ":"), $A$5:$B$8, 2, FALSE))

Or else the formula wont for the 2nd row.

Dratsab

3404 posts

Uber Geek

Trusted

Lifetime subscriber

# 249652 22-Aug-2009 13:42

Another way of writing it would be:

=IF(ISERROR(VLOOKUP(B12&":",$A$5:$B$8,1,FALSE)),"",VLOOKUP(B12&":",$A$5:$B$8,2,FALSE))

Apart from the two instances of &":" and the absolute references in the formula, nzjwnz was there ;-)

Dratsab

3404 posts

Uber Geek

Trusted

Lifetime subscriber

# 249664 22-Aug-2009 14:39

Oh yeah - almost forgot to say...named ranges are better to use than absolute references. In your case, you should name the range A5:B9 as something like Trade. Then substitute both instances of $A$5:$B$9 in your formula with the word Trade.

Using a named range like this means that if you expand your listing of trade services, all you will need to do is adjust the named range - your formula won't need touching.

1 | 2