Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


astrobox

23 posts

Geek


#39840 20-Aug-2009 11:15
Send private message

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 :(

View this topic in a long page with up to 500 replies per page Create new topic
 1 | 2
nzjwnz
40 posts

Geek


  #248972 20-Aug-2009 11:25
Send private message

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
Send private message

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

Jimmyoo0
19 posts

Geek


  #248976 20-Aug-2009 11:33
Send private message

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
Send private message

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
40 posts

Geek


  #248983 20-Aug-2009 11:44
Send private message

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
1286 posts

Uber Geek


  #248985 20-Aug-2009 11:45
Send private message

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
Send private message

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 :)

 
 
 

GoodSync. Easily back up and sync your files with GoodSync. Simple and secure file backup and synchronisation software will ensure that your files are never lost (affiliate link).
nzjwnz
40 posts

Geek


  #248990 20-Aug-2009 11:55
Send private message

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
Send private message

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
Send private message

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
1286 posts

Uber Geek


  #248997 20-Aug-2009 12:11
Send private message

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
Send private message

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

rvangelder
352 posts

Ultimate Geek


  #249555 21-Aug-2009 21:59
Send private message

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
3946 posts

Uber Geek

Trusted
Lifetime subscriber

  #249652 22-Aug-2009 13:42
Send private message

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
3946 posts

Uber Geek

Trusted
Lifetime subscriber

  #249664 22-Aug-2009 14:39
Send private message

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
View this topic in a long page with up to 500 replies per page Create new topic





News and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.