Help with complex formula - H and VLOOKUP

**supraruss** · 07-01-2008, 09:56 AM

Hello everyone,

Hoping some of you may be able to assist with a formula I am trying to work out (apologies if I have posted in the wrong section as this is my first post).

If I explain what I need to do, hopefully someone may be able to assist?

We have 4 levels of hourly rate and lets say 100, 200, 300 and 400 to make things simple. The rate depends on the person working on the client, so 100 would be a consultant and 400 a director.

We have certain clients that we agree a set fee so that anyone working on them, we use an exception and we have a formula to find this. What we do is copy the name of the client into a table and add the new set rate and our formula looks at this table and takes the set rate from here. If the client name isn't in the table, it takes the standard rate from a data sheet depending on consultant.

What I now need to do is try and find out a way to lookup so we can use a changed variable rate - so if one consultant is usually 200 we have agreed 250 for them and another that is 400 we have agreed 350 and so on.

Its very confusing if you haven't seen the spreadsheet, but hoping there is a way of getting the spreadsheet, or a printscreen posted, if anyone can help?

forgot to add the formula so far (??? is what I need to work out to find a variable rate)

F(ISERROR(VLOOKUP(B" & i & ",exeptions,2,FALSE)),???(VLOOKUP(S" & i & ",rate,HLOOKUP(A" & i & ",since,2,TRUE),FALSE)),VLOOKUP(B" & i & ",exeptions,2,FALSE))

Thanks very much

Russ

**NBVC** · 07-01-2008, 09:59 AM

I think you will need to attach a sample sheet... use the paperclip icon in the Response box.

**supraruss** · 07-01-2008, 10:04 AM

attached is sample sheet - please note this isn't the full working system we have as that is a 30mb file and can't be attached but if I can work out how to find the differing amounts (see Test project) then problem solved!

Thanks

**supraruss** · 07-01-2008, 10:30 AM

Forgot to mention, any help suggestions etc would be most welcome as I am not 100% sure that it is even possible to do this, as never really used lookup before and certainly not to this extent.

Thanks

**TheNorm** · 07-01-2008, 10:36 AM

Do you want logic like this?

IF there is an exception, apply that rate; if not, apply the standard rate (according to grade)

**supraruss** · 07-01-2008, 10:44 AM

Originally Posted by TheNorm

Do you want logic like this?

IF there is an exception, apply that rate; if not, apply the standard rate (according to grade)

That's what I've been trying to work out - So I can use the IF formula -

So what I need to do is to say if Test Project has the word 'different' in the rate column it means that the rate will be different than the set amount already in our system so what I need to do then is find a way of doing the following -

If rate is different, lookup the standard rate and then change it to the rate entered so if standard was 250 and the entered varying rate was 275 it would change this as well as another rate for the same project, say 230 and change it to 200.

I have very limited knowledge of Excel so any help in terms of formula to try would be most gratefully received.

Thanks

Russ

**robert_shindorf** · 07-01-2008, 10:44 AM

hey supraruss,

i have a fairly complex spreadsheet for a consulting firm I made already (i'm a consultant also) with varying rates, etc. etc. etc.

I'm not sure what the end result you are trying to get is, but mine may interface into your's, or maybe there is a work around to what you are trying to actually achieve...

if you private message me, i'll send you my email and you can zip and upload your file, and I'll try to add-in my template for you.

robert

**NBVC** · 07-01-2008, 10:47 AM

I'm a little confused... I see your Vlookup formulas...but am not sure what you finally want...

Does this formula in J22 do what you need? =INDEX(C2:F41,MATCH(J13,Client,0),MATCH(L13,C1:F1,0))

**supraruss** · 07-01-2008, 12:04 PM

Originally Posted by NBVC

I'm a little confused... I see your Vlookup formulas...but am not sure what you finally want...

Does this formula in J22 do what you need? =INDEX(C2:F41,MATCH(J13,Client,0),MATCH(L13,C1:F1,0))

Whoa, I think we are on to something here! That's brill, did it take you long to come up with that? Never really used match before.

I am waiting for comfirmation from another chap to see if we can impliment this into the existing system and bingo!

Thanks so much for your guidance. When you said cell J22 I was trying to get the formula you have written but obviously completely different!

Help with complex formula - H and VLOOKUP

LinkBack

Thread Tools

Rate This Thread

Display

Help with complex formula - H and VLOOKUP

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions